Главная » Бесплатные рефераты » Бесплатные рефераты по информатике »

Создание списков, сводных таблиц и диаграмм в Microsoft Excel 2003

Тема: Создание списков, сводных таблиц и диаграмм в Microsoft Excel 2003

Раздел: Бесплатные рефераты по информатике

Лабораторная работа № 2

Создание списков, сводных таблиц и диаграмм в среде табличного процессора Microsoft Excel 2003.

1. Цель работы: ознакомление с возможностями работы табличного процессора со списком данных, приобретение навыков создания списков (базы данных) пользователя, свободных таблиц и диаграмм.

2. Технология работы с табличным процессором

Задание 1. Подключите надстройки Microsoft Excel

нажимаем кнопку Пуск и выбираем в Главном меню команды Программы\MS Excel;
выбираем в строке меню команды Сервис\Надстройки;
устанавливаем флажки, как показано на рис. 1;
нажимаем кнопку ОК.

переименуем лист 1 в СчетФактура;
вводим в ячейку 10 – название поля списка – Номер Счет_Фактура;
вводим в ячейку 10 – название поля списка - Дата;
вводим в ячейку 10:10 – название полей, приведенные на рис. 2;
вводим в ячейку 11 формулу =11*11 расчета стоимости товаров;
вводим в ячейку 11 формулу =11*11 расчета суммы налогов;
вводим в ячейку 11 формулу =11+11 расчета стоимости товаров с учетом налогов;
выбираем для ячейки 11 формат Процентный и введем число 18;
вводим в ячейки 1:11, 11 – информацию, приведенную на рис.2.

Задание 3. Создайте Форму ввода и редактирования данных списка СчетФактура

активизируем любую ячейку списка;
выбираем в строке меню команды Данные\Форма... (рис.3);
нажимаем кнопку Добавить;
вводим данные, приведенные на рис.4, для перемещения к следующему полю формы используем клавишу и сочетание клавиш + для перемещения к предыдущему полю;
чтобы добавить запись в список, нажимаем клавишу ;
по завершению набора последней записи нажмите кнопку Закрыть.

Задание 4. Используя Форму, найти запись с наименованием товара – Масло ИГП

активизируем любую ячейку в списке;
выбираем в строке меню команды Данные\Форма...;
нажимаем кнопку Критерии;
вводим в поле Наименование товара критерий поиска – Масло ИГП;
чтобы найти совпадающие с критериями записи, нажимаем кнопки Далее или Назад (рис.5);
для возврата к правке формы нажимаем кнопку Правка.

Задание 5. Используя Автофильтр, найти запись с номером СчетаФактуры – 1629

активизируем любую ячейку в списке;
выбираем в строке меню команды Данные\Фильтр\Автофильтр;
используя кнопку автофильтра, в поле Номер СчетФактура, выбираем условие отбора – 1629 (рис.6).

Задание 6. Отберите записи по условию Цена за ед. изм. лежит в пределах от 17 000р. до 21 800р.

нажимаем кнопку в поле Цена за ед. изм. и выбираем команду Условие;
в окне Пользовательский автофильтр устанавливаем параметры отбора записей, как показано на рис. 7;

нажмите кнопку ОК (рис.8);
для восстановления списка нажимаем кнопку в поле Цена за ед. изм. и выбираем команду Все.

Задание 7. Используя Расширенный фильтр, найдите записи, удовлетворяющие условию: количество товара больше 50 тонн и меньше 100 тонн, а цена за ед. изм. больше 21 500р. и меньше 22 000р.

вводим в ячейки 1:2 информацию, приведенную на рис. 9;
активизируем любую ячейку в списке;
выбираем в строке меню команды Данные\Фильтр\Расширенный фильтр;
в окне Расширенный фильтр устанавливаем опцию фильтровать список на месте;
в поле Диапазон условий вводим адреса ячеек 1:2 и нажимаем кнопку ОК (рис.10);
для восстановления списка выбираем в строке меню команды Данные\Фильтр\Отобразить все.

Задание 8. Отсортируйте список по возрастанию по полю Наименование товара

активизируем любую ячейку списка;
выбираем в строке меню команды Данные\Сортировка...;
в окне Сортировка диапазона в поле Сортировать по выбираем из списка полей Кол-во иустанавливаем опцию по возрастанию;
нажимаем кнопку ОК;
подведение итогов (рис. 12)

активизируем любую ячейку списка;
выбираем в строке меню команды Данные\Итоги;
в окне Промежуточные итоги задаем параметры, как на рис. 13;
нажимаем кнопку ОК;
нажимаем кнопки, первого ряда, расположенные слева от таблицы (рис.15)

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

выбираем в строке меню команды Данные\Итоги;
в окне Промежуточные итоги нажимаем кнопку Убрать все.

Задание 11. Создайте сводную таблицу, содержащую в наименовании строк содержание поля Наименование товара, в наименовании столбцов содержание поля Дата, внутри таблицы сумму содержания поля Кол-во

активизируем любую ячейку списка;
выбираем в строке меню команды Данные\Сводная таблица;
в окне Мастер свободных таблиц и диаграмм шаг 1 нажимаем кнопку Далее;
в окне Мастер свободных таблиц и диаграмм шаг 2 нажимаем кнопку Далее;
в окне Мастер свободных таблиц и диаграмм шаг 3 нажимаем кнопку Макет;
в окне Мастер свободных таблиц и диаграмм - Макет перетаскиваем поля, как на рис.16.
нажимаем кнопку ОК;
нажимаем кнопку Готово;
переименуем лист со сводной таблицей – Продажи по дням (рис.17).

Задание 12. Используя сводную таблицу Продажи по дням, получить данные для товара – Масло ИГП, затем для даты – 09.11.2005.

сделаем активным лист Продажи по дням;
нажимаем кнопку в поле Наименование товара;
оставьте флажок только у Масло ИГП (рис. 18);
нажимаем кнопку ОК;
повторяем описанные выше действия для поля – Дата (рис.19);
восстанавливаем все флажки в обоих полях.

Задание 13. Добавьте в наименование строк поле Цена

вызовем панель инструментов Сводная таблица, если она отсутствует на экране;
активизируем ячейку внутри сводной таблицы;
перетащим поле Цена за… с панели инструментов на наименование строк сводной таблицы;

Сводная таблица принимает вид, показанный на рис. 21

Задание 14. Постройте Сводную диаграмму для задания 11.

сделаем активным лист СчетФактура;
активизируем любую ячейку списка;
выбираем в строке меню команды Данные/Сводная таблица;
выбираем опцию Сводная диаграмма (со сводной таблицей);
нажимаем кнопку Далее;
в следующем окне нажмем кнопку Далее;
на запрос нажимаем кнопку Да;
в следующем окне нажимаем кнопку Далее;
нажимаем кнопку Макет и построим макет сводной таблицы для тех же полей, что и ранее (рис.22).
нажимаем кнопку ОК, затем кнопку Готово.

Задание 15. Создайте список СправочникБанков.

вставим лист и присвоим ему имя СправочникБанков.
в ячейках 1:1 создаем поля списка: КодБанка, Название, Индекс, Город, Улица, Дом, Строение;
для проверки названия городов введем их наименования в столбец (рис. 23);
выделяем столбец и выбираем в строке меню команды Данные\Проверка;
в окне Проверка вводимых значений выбираем вкладку Параметры;
задаем параметры проверки, как представлено на рис. 24;
вводим информацию, представленную на рис.25;
выделяем ячейки 2:5 и выбираем в строке меню команды Вставка\Имя\Присвоить;
в окне Присвоение имени в поле Имя вводим Банки и нажимаем кнопку ОК (рис.26).

Задание 16. Создайте список СправочникПродавца.

вставим лист и присвоим ему имя СправочникПродавца;
в ячейках 1:1 создаем поля списка: КодПродавца, НазваниеФирмы, Индекс, Город, Улица, Дом, Строение, Телефон, ИНН/КПП, РасчетныйСчет, БИК, КорСчет, КодБанка, НазваниеБанка;
для проверки названия городов вводим их наименования в столбец ;
выделяем столбец и выбираем в строке меню команды Данные\Проверка;
в окне Проверка вводимых значений выбираем вкладку Параметры;
для связи таблицы СправочникБанков с таблицей СправочникПродавца в ячейку 2 введем формулу =ВПР(2; Банки;2;1)
скопируем формулу из ячейки 2 в ячейки 3, 4,….(не обращаем внимания на сообщения #Н/Д) (рис.27)
вводим информацию, представленную на рис. 28

Задание 17. Создайте список СправочникПокупателя.

вставим лист и присвоим ему имя СправочникПокупателя;
вводим поля, как у предыдущего списка;
организуем проверку ввода названий городов;
связываем список СправочникБанков со списком СправочникПокупателя;
вводим информацию, представленную на рис.29

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

Какие операции включает структурирование списка?

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

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

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

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

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

Какие операции включает в себя отбор записей из списка по условию?

Чтобы выполнить отбор записей из списка по условию необходимо:

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

Какие операции необходимо выполнить для автоматического подведения промежуточных итогов и общих итогов?

Чтобы автоматически подвести промежуточные итоги нужно:

отсортировать таблицу по столбцу, содержащему группы, по которым надо подвести итоги;
установить курсор в любую ячейку этого столбца;
выбрать в строке меню команды Данные/Итоги;
в поле «При каждом изменении в…» указать столбец с группами, по которым надо подводить итоги;
в поле «Использовать функцию…» указать СУММА;
в перечне «Добавить итоги по…» указать столбцы, значения в которых должны быть просуммированы;
нажать кнопку ОК.

Как организовать отбор данных с помощью Расширенного автофильтра?

Отбор данных с помощью Расширенного фильтра может быть организован при выполнении следующих операций:

создать область критериев таким образом, чтобы она не мешала дополнению и расширению списка. Область критериев представляет собой минимум 2 строки, в первой из которых содержатся названия полей из заглавной строки списка, а в остальных строках указываются критерии поиска.
для установки нескольких критериев для одного поля (логическое И), в интервал критериев должно быть включено несколько столбцов с названием этого поля.
если на экран надо вывести записи, удовлетворяющие одному из критериев (логическое ИЛИ), то ввод условий производиться в разные строки одного столбца.
установить курсор в любую ячейку списка и задать команду Данные/Фильтр, а затем выбрать пункт Расширенный фильтр.
включить параметр «Фильтровать список на месте», если результат фильтрации будет располагаться на том же месте, где и сам список или параметр «Скопировать результат в другое место», если результат нужно поместить в целевую область. Целевую область тоже следует располагать так, чтобы избежать конфликтов с частями таблицы, выделенными под список и критерии. В первой строке целевой области следует привести имена полей, содержимое которых нужно увидеть в найденных записях (порядок и количество полей может быть произвольным).
в поле «Диапазон критериев» указать диапазон тех ячеек, где размещается область критериев.
если требуется поместить результат в целевую область, то в поле «Поместить результат в диапазон» следует указать диапазон, содержащий заголовок целевой области.
нажать кнопку ОК.

Как построить Сводную таблицу?

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

установить курсор в любую ячейку списка и выбрать команды Данные/Сводная таблица.
в открывшемся диалоговом окне «Мастер сводных таблиц» отметить опцию в списке или базе данных Microsoft Excel.
далее определить диапазон, с которым будет работать Мастер сводных таблиц. Щелкнуть на кнопке Далее.
в следующем окне определить, значения каких полей списка будут использоваться в качестве заголовков строк (зона Строка), каких – в качестве заголовков столбцов (зона Столбец) и каких – в качестве данных (зона Данных), по которым следует подвести необходимые итоги. В зону Страница помещается кнопка поля, по которому предполагается фильтровать данные. В каждой зоне может быть несколько кнопок. Для того чтобы в новой таблице получить только итоговые значения, следует все зоны, кроме зоны Данные, оставить пустыми.
далее определяется место, в которое будет помещена разработанная сводная таблица (например, ячейка нового листа). Там же определяются параметры сводной таблицы: следует ли выводить общие итоги по строкам и по столбцам.
после нажатия на кнопку Готово в указанном месте появляется таблица со сводными данными.

Как можно изменить структуру Сводной таблицы?

Чтобы изменить структуру сводной таблицы нужно:

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

Задание для самостоятельной работы

Задание 1. Создайте список СправочникРегионов

переименуем Лист 1 в Справочник регионов;
вводим в ячейку А1 – название поля списка - Код региона;
вводим в ячейку В1 – название поля списка - Наименование региона;
вводим в ячейки А2:В80 информацию, приведенную на рис. 30.

Задание 2. Создайте список из двадцати кредитных организаций

переименуем Лист 2 в Справочник кредитных организаций;
вводим в ячейки А1:F21 информацию, приведенную на рис. 31.

Задание 3. Используйте функцию ВПР для связи двух таблиц

для связи таблицы Справочник Регионов с таблицей Справочник кредитных организаций в ячейку G2 вводим формулу =ВПР(B2;СправочникРегионов!$A$2:$B$80;2;1);
остальные ячейки заполняем с помощью маркера автозаполнение (рис.32)

Задание 4. Рассчитайте коэффициент риска региона по формуле: Коэффициент риска региона = Сумма кредитных вложений по региону / (Величина математического ожидания риска*Коэффициент кредитоспособности региона)

в ячейку F2 вводим формулу = C2/(D2*E2)
остальные ячейки заполняем с помощью маркера автозаполнение (рис. 34)

Задание 5. Расположите кредитные организации в алфавитном порядке.

активизируем любую ячейку списка;
выбираем в строке меню команды Данные\Сортировка...;
в окне Сортировка диапазона в поле Сортировать по выбираем из списка полей Кредитные организации иустанавливаем опцию по возрастанию;
нажимаем кнопку ОК;
подведение итогов (рис. 35)

Задание 6. Отберите записи с использованием Автофильтра, в которых в поле Сумма кредитных вложений по региону больше 1200 млн. руб. и меньше 2500 млн. руб.

активизируем любую ячейку списка;
выбираем в строке меню команды Данные\Фильтр\Автофильтр
нажимаем кнопку в поле Сумма кредитных вложений и выбираем команду Условие;
в окне Пользовательский автофильтр устанавливаем параметры отбора записей, как показано на рис. 36;
нажимаем кнопку ОК;
подведение итогов (рис. 37)
для восстановления списка нажимаем кнопку в поле Сумма кредитных вложений и выбираем команду Все.

Задание 7. Подведите итоги для каждого региона по кредитным вложениям.

активизируем любую ячейку списка;
выбираем в строке меню команды Данные\Итоги;
в окне Промежуточные итоги задаем параметры, как на рис. 38;
нажимаем кнопку ОК.

Результаты подведения итогов приведены на рис. 39

Задание 8. Создайте сводную таблицу, состоящую из кредитных организаций, кода региона, кредитных вложений

активизируем любую ячейку списка;
выбираем в строке меню команды Данные\Сводная таблица;
в окне Мастер свободных таблиц и диаграмм шаг 1 нажимаем кнопку Далее;
в окне Мастер свободных таблиц и диаграмм шаг 2 нажимаем кнопку Далее;
в окне Мастер свободных таблиц и диаграмм шаг 3 нажимаем кнопку Макет;
в окне Мастер свободных таблиц и диаграмм - Макет перетаскиваем поля, как показано на рис.16.
нажимаем кнопку ОК;
нажимаем кнопку Готово.

Внимание!

Если вам нужна помощь в написании работы, то рекомендуем обратиться к профессионалам. Более 70 000 авторов готовы помочь вам прямо сейчас. Бесплатные корректировки и доработки. Узнайте стоимость своей работы

Бесплатная оценка

Размер: 3.11M

Скачано: 389

Скачать бесплатно

20.12.11 в 20:36 Автор:

Понравилось? Нажмите на кнопочку ниже. Вам не сложно, а нам приятно).

Чтобы скачать бесплатно Лабораторные работы на максимальной скорости, зарегистрируйтесь или авторизуйтесь на сайте.

Важно! Все представленные Лабораторные работы для бесплатного скачивания предназначены для составления плана или основы собственных научных трудов.

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

Добавить работу

Если Лабораторная работа, по Вашему мнению, плохого качества, или эту работу Вы уже встречали, сообщите об этом нам.

Добавление отзыва к работе