Studrb.ru банк рефератов
Консультация и поддержка студентов в учёбе

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

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

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

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

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

Тип: Лабораторная работа | Размер: 3.11M | Скачано: 296 | Добавлен 20.12.11 в 20:36 | Рейтинг: +1 | Еще Лабораторные работы


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

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

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

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

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

  1. нажимаем кнопку Пуск и выбираем в Главном меню команды Программы\MS Excel;
  2. выбираем в строке меню команды Сервис\Надстройки;
  3. устанавливаем флажки, как показано на рис. 1;
  4. нажимаем кнопку ОК.
  1. переименуем лист 1 в СчетФактура;
  2. вводим в ячейку 10 – название поля списка – Номер Счет_Фактура;
  3. вводим в ячейку 10 – название поля списка - Дата;
  4. вводим в ячейку 10:10 – название полей, приведенные на рис. 2;
  5. вводим в ячейку 11 формулу =11*11 расчета стоимости товаров;
  6. вводим в ячейку 11 формулу =11*11 расчета суммы налогов;
  7. вводим в ячейку 11 формулу =11+11 расчета стоимости товаров с учетом налогов;
  8. выбираем для ячейки 11 формат Процентный и введем число 18;
  9. вводим в ячейки 1:11, 11 – информацию, приведенную на рис.2.

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

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

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

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

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

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

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

  1. нажимаем кнопку в поле Цена за ед. изм. и выбираем команду Условие;
  2. в окне Пользовательский автофильтр устанавливаем параметры отбора записей, как показано на рис. 7;
  1. нажмите кнопку ОК (рис.8);
  2. для восстановления списка нажимаем кнопку в поле Цена за ед. изм. и выбираем команду Все.

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

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

 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Внимание!

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

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

+1
Размер: 3.11M
Скачано: 296
Скачать бесплатно
20.12.11 в 20:36 Автор:

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


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

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


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

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


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


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

Добавить отзыв могут только зарегистрированные пользователи.


Похожие работы

Консультация и поддержка студентов в учёбе