Создание структуры при подведении итогов
Откройте книгу Финансовый отчёт за первый квартал , созданную в лабораторной работе №1, создайте в ней новый лист, дав ему название Итоги.
Скопируйте на лист Итоги таблицу с листа Список. Требуется подвести общие и промежуточные итоги для фирм производителей (поле Фирма) по полям «Количество проданных», «Издержки», «Доход», «Прибыль».
o Сделайте активной ячейку А1. Примените команду Сортировать по возрастанию – вкладка ГЛАВНАЯ
o Сделайте активной ячейку А36 и примените команду ДАННЫЕ / ПРОМЕЖУТОЧНЫЕ ИТОГИ
o В окне «ИТОГИ» сделайте следующие установки:
Из списка «ПРИ КАЖДОМ ИЗМЕНЕНИИ В» выберите поле «Фирма»;
В списке «ОПЕРАЦИЯ» - сумма;
В списке «ДОБАВИТЬ ИТОГИ ПО» установите флажки слева от полей «Количество продаж», «Издержки», «Доход», «Прибыль»;
Щелкните по кнопке ОК.
o Выделите цветом значения итогов в различных фирмах
o Другим цветом выделите общий итог
При подведении итогов автоматически создается структура таблицы.
На листе Итоги символы структуры отображаются слева от номеров строк. Имеется три уровня структуры. Щелчок по номеру уровня позволяет скрыть детали уровней с большими номерами и показать детали этого уровня и всех уровней с меньшими номерами.
Для того, чтобы показать все данные в таблице, нужно щелкнуть по самому большому номеру - 3. Чтобы скрыть детальные данные всех уровней структуры, нужно щелкнуть по номеру 1. Данные уровня 1 скрытыми быть не могут.
|
|
|
Исследуйте возможности работы со структурой таблицы. Скройте все детальные данные и отобразите только результаты подведения итогов.
Выполнение вычислений и построение диаграмм на основе итоговых данных
Для определения доли продажи каждого из товаров в той или иной фирме относительно общего количества всех продаж в данной фирме выполните следующие расчёты.
Сделайте активной ячейку I1, введите название поля «Доля продажи товара в фирме», щелкнув в ячейке H1 и по кнопке «Формат по образцу» на вкладке ГЛАВНАЯ, примените формат к ячейке I1, щёлкнув на ней мышью.
Выполним расчёты для фирмы Alcatel. В ячейку I2 введите формулу D2/$D$6, которую скопируйте на диапазон I2:I5 с помощью автозаполнения. То же самое сделайте для оставшихся фирм.
Создание диаграммы
Выделите столбцы B, C, D и из контекстного меню примените команду СКРЫТЬ. Выделите диапазон ячеек A1:Е44 и на вкладке ВСТАВКА выберите вид диаграммы (гистограмма, например). Диаграмме дайте название «Издержки за 1 квартал», легенду удалите клавишей Del.
Диаграмму можно обновлять, изменяя данные в таблице, а также скрывать или показывать данные в соответствии с тем, скрываются или показываются с помощью символов структуры отдельные детали таблицы.
|
|
|
Откройте в таблице 3 группу детальных данных и обратите внимание на изменения на диаграмме.
Создание отчета на основе сводной таблицы
В отчёте отразим количество продаж, доход и прибыль товаров различных фирм с указанием их цены.
Создайте новый лист с именем Отчет.
Сделайте активной ячейку А1 и примените команду ВСТАВКА /СВОДНАЯ ТАБЛИЦА. В окне диалога СОЗДАНИЕ СВОДНОЙ ТАБЛИЦЫ укажите в строке ДАННЫЕ ДЛЯ АНАЛИЗА таблицу на листе СПИСОК.
На панели СПИСОК ПОЛЕЙ СВОДНОЙ ТАБЛИЦЫ выберите поля для отчётной таблицы, установив флажки в следующих строках:
ФИРМА, МОДЕЛЬ, ЦЕНА, КОЛИЧЕСТВО ПРОДАННЫХ, ДОХОД, ПРИБЫЛЬ.
По умолчанию осуществляется операция суммирования по столбцам, помещённым в область данных.
Переместим мышью поле ФИРМА из области НАЗВАНИЯ СТРОК в область ФИЛЬТР ОТЧЁТА.
Переместим мышью поле ЦЕНА из области НАЗВАНИЯ СТОЛБЦОВ в область НАЗВАНИЯ СТРОК.
Поместив курсор в поле КОЛИЧЕСТВО ПРОДАННЫХ, откройте окно диалога ПАРАМЕТРЫ ПОЛЯ ЗНАЧЕНИЙ из списка АКТИВНОЕ ПОЛЕ со строки ПАРАМЕТРЫ ПОЛЯ. В окне ПАРАМЕТРЫ ПОЛЯ ЗНАЧЕНИЙ выберите операцию МАКСИМУМ.
То же самое сделайте для полей ДОХОД и ПРИБЫЛЬ.
|
|
|
Поместите курсор в таблицу и на вкладке КОНСТРУКТОР из списка ПРОМЕЖУТОЧНЫЕ ИТОГИ выберите строку НЕ ПОКАЗЫВАТЬ ПРОМЕЖУТОЧНЫЕСУММЫ.
На вкладке КОНСТРУКТОР примерьте таблице различные стили из галереи стилей.
Поместите курсор в таблицу и, щелкнув по кнопке СВОДНАЯ ТАБЛИЦА, со строки ПАРАМЕТРЫ примените команду ОТОБРАЗИТЬ СТРАНИЦЫ ФИЛЬТРА ОТЧЁТА.
Выбирая ту или иную страницу отчёта, можно проследить как изменяется прибыль и доход от цены и количества продаж товаров в разных фирмах.
Защита данных
Защита листа
Откройте лист 1 КВАРТАЛ и примените к нему команду РЕЦЕНЗИРОВАНИЕ /ЗАЩИТИТЬ ЛИСТ. В окне Защита листа пароль вводить не надо. Проверьте действие защиты листа.
Защита диапазона ячеек
1. Откройте лист «СПИСОК»
2. Выделите таблицу и откройте окно «ФОРМАТ ЯЧЕЕК». Для этого щёлкните на кнопке ФОРМАТ вкладки ГЛАВНАЯ и выберите строку ФОРМАТ ЯЧЕЕК
3. На вкладке ЗАЩИТА окна ФОРМАТ ЯЧЕЕК удалите флажок в поле «Защищаемая ячейка»
4. Выделите столбец «Цена» и примените команду ФОРМАТ/ ФОРМАТ ЯЧЕЕК. На вкладке Защита окна ФОРМАТ ЯЧЕЕК установите флажок в поле Защищаемая ячейка.
5. В заключении примените команду РЕЦЕНЗИРОВАНИЕ / ЗАЩИТИТЬ ЛИСТ
Проверьте результат защиты.
|
|
|
Самостоятельная работа
1. Создайте новый лист, дав ему название МОИ ИТОГИ. На лист скопируйте таблицу с листа СПИСОК. Установите курсор в таблицу и подведите общие и промежуточные итоги для столбцов КОЛИЧЕСТВО ПРОДАННЫХ, ИЗДЕРЖКИ, ПРИБЫЛЬ, применив операцию Среднее.
Последовательно установите курсор сначала в ячейку с общим средним для столбца КОЛИЧЕСТВО ПРОДАЖ и присвойте ячейке имя «среднее_количество_продаж», а затем в ячейку со средней прибылью столбца ПРИБЫЛЬ и присвойте ей имя «средняя_прибыль».
2. Постройте диаграмму для значений средней прибыли фирм производителей, скрыв ненужные столбцы.
3. Создайте новый лист, который назовите Мой отчет и создайте свой отчет. В качестве исходных данных используйте таблицу с листа Список.
Указания: поместите в фильтр поле ФИРМА, в область строк - поле МОДЕЛЬ, в область значений - ЦЕНА, ПРИБЫЛЬ и примените к «цене» операцию «минимум», к «прибыли» - максимум.
Вопросы для самоконтроля
1. Что следует сделать перед автоматическим подведением итогов по столбцам?
2. Что следует сделать, чтобы при многократном подведении итогов ранее созданные итоги оставались не заменялись новыми?
3. Что автоматически происходит с таблицей наряду с подведением общих и промежуточных итогов?
4. Какой командой можно скрыть/ показать столбцы таблицы?
5. Как изменяется вид диаграммы при скрытии или показе уровней структуры таблицы?
6. Как запустить мастер Сводной таблицы?
7. Какие операции можно применить к значениям при создания сводной таблицы?
8. Какая операция применяется к значениям в сводной таблице по умолчанию?
9. Как защитить ячейки таблицы?
10. Как убрать защиту ячеек?
Тема: Фильтрация данных. Осуществление межтабличных связей.. Создание прайс - листа
Цель:
o Знакомство с условиями использования Автофильтра и Расширенного фильтра. Работа с окнами диалога «Пользовательский фильтр» и «Расширенный фильтр».
o Осуществление межтабличных связей.
o Создание прайс – листа.
Время проведения: 2 часа.
Вопросы для подготовки:
11. Работа с таблицей как базой данных.
12. Фильтрация данных в таблице. Автофильтр и расширенный фильтр.
13. Использование процедуры подбора параметра?
Литература:
17. Экономическая информатика/ Под ред. П.В. Конюховского и Д.Н. Колесова. – СПб: Питер, 2000. – 560 с.
18. Базовый курс Excel. Изучаем Ms Office. М: изд-во Современная школа, 2007
19. Сергеев Александр Петрович. Использование Microsoft Office Excel 2007, - М: Диалектика, 2007
20. Грег Харвей. Microsoft Office Excel 2007 для "чайников". Полный справочник, - м: диалектика, 2008
21. Пол Мак-Федрис. Формулы и функции в Microsoft Office Excel 2007, - М: Вильямс, 2008
22. Сингаевская Галина Ивановна. Функции в Microsoft Office Excel 2007, - М: Диалектика, 2008
23. Джон Уокенбах. Microsoft Office Excel 2007. Библия пользователя, – М: Диалектика, 2008
24. Глушаков С.В. Microsoft Office 2007. Лучший самоучитель. М.:АСТ: АСТ МОСКВА; Владимир: ВКТ, 2008.
Выборка данных
В Microsoft Excel предусмотрены различные методы анализа данных в списке. К списку можно применить фильтр, чтобы отобрать только те записи, которые соответствуют определенным условиям. Для этого служат команды Автофильтр и Расширенный фильтр.
Откройте книгу Финансовый отчет. Добавьте лист и поименуйте его Фильтры. Откройте лист Список, скопируйте в буфер обмена таблицу, содержащуюся на нем, и вставьте её на лист Фильтры. Дополните таблицу столбцом Гарантийный срок, куда введите вымышленные данные, например, срок гарантии может быть 1, 2, 3, 6 или 12 месяцев.
Фильтры
Команду ФИЛЬТР можно использовать как для сортировки, так и для отображения значений, удовлетворяющих определённому условию. Чтобы применить команду ФИЛЬТР нужно на ленте вкладки ДАННЫЕ щёлкнуть по кнопке ФИЛЬТР и далее по кнопке раскрывающегося списка в заголовке того столбца, по которому необходимо осуществить фильтрацию. В результате получим окно диалога, отображённое ниже.
С помощью этого окна можно отобразить данные, удовлетворяющие одному из условий перечисленных на выпадающем меню строки ЧИСЛОВЫЕ ФИЛЬТРЫ .
Чтобы отобразить значения, удовлетворяющие условиям, составленным с использованием логических операторов and (и) или or (или) используют строку НАСТРАИВАЕМЫЙ ФИЛЬТР на выпадающем меню.
Отобразим сотовые телефоны с гарантийными сроками, равными 1 или равными 6 месяц.
1. Поместите указатель курсора в список.
2. Выберите пункт ФИЛЬТР на вкладке ДАННЫЕ
3. Нажмите кнопку со стрелкой в заголовке столбца ГАРАНТИЙНЫЙ СРОК
4. Со строки ЧИСЛОВЫЕ ФИЛЬТРЫ откройте ПОЛЬЗОВАТЕЛЬСКИЙ АВТОФИЛЬТР и сделайте установки как ниже
Расширенный фильтр
Расширенный фильтр используется, если требуется скопировать записи в другое место или отобрать данные на основе вычисленного значения.
Для расширенного фильтра необходимо создать диапазон условий. Диапазон условий состоит из заголовка таблицы (списка) и строк под ним. В ячейки столбцов, значения в которых должны удовлетворять определённым условиям вводятся выражения. Выражения могут быть простыми как в автофильтре или вычисляемыми.
Выражения, введённые в разные столбцы одной строки, объединяются логическим «и», в разные строки одного столбца – логическим «или».
Задание. Требуется отфильтровать записи, содержащие товары фирмы Samsung ценой не более 3000 руб, с гарантийным сроком не менее 3 мес.
§ На листе Фильтры выделите заголовок таблицы и поместите его в буфер обмена.
§ Сделайте активной ячейку А37 и вставьте содержимое буфера обмена.
§ В следующие ячейки введите условия фильтрации:
А38 – Samsung
С38 - <=10000
I38 - >=1
§ Поместите курсор в таблицу, являющуюся списком, и примените команду ДОПОЛНИТЕЛЬНО
§ В открывшемся окне диалога введите следующие параметры:
Фильтры с вычисляемыми условиями. Отфильтруйте записи на основании условия создаваемого как результат выполнения формулы. Например, создайте список товаров, прибыль от продажи которых превышает среднюю прибыль. Для этого:
§ Скопируйте заголовок исходного списка в ячейку А50.
§ Щелкнув в ячейке G51, введите условие отбора:
=G2> СрЗнач ($G$2:$G$35)
§ Удалите подпись Прибыль в столбце заголовка условий отбора. У вас должно получиться следующее:
§ Поместите курсор в исходный список и примените команду ДАННЫЕ / ДОПОЛНИТЕЛЬНО
§ Сделайте установки в окне диалога РАСШИРЕННЫЙ ФИЛЬТР. Поместите результат в ячейку А53.
§ Выделив два несмежных столбца МОДЕЛЬ и ПРИБЫЛЬ в образовавшемся списке, постройте круговую диаграмму, на которую поместите подписи данных.
Дата добавления: 2018-04-05; просмотров: 170; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!