практическая работа № 5. ФОС_Практическая_работа_№_5_Работа_с_электроннымы_таблицами. Работа в редакторе электронных таблиц (16 ч)
 Скачать 3.61 Mb.
|
Задание 9. Создание и работа с диаграммами и графикамиРассмотрим в качестве примера таблицу. (Файл Кейс7.xlsx) Имеем информацию о продажах каждого менеджера за пять месяцев. (Рисунок 1)  Рисунок 1 Построим и опишем самые полезные графики. Выделяем всю область таблицы Инструменты для создания графиков расположены на панели меню "Вставка" Выбираем соответствующий тип графика Гистограммы Данный вид графиков следует использовать для сравнения данных как в абсолютных значениях, так и в относительных. Есть несколько типов гистограмм: Гистограмма с группировкой Используется для сравнения. Ее вид представлен на рисунке 2.  Рисунок 2 Гистограммы с накоплением Данные отображаются как часть одного целого. Наглядно видно влияние каждого элемента на общий результат. (Рисунок 3)  Рисунок 3 Нормированная гистограмма с накоплением Отражает данные всех категорий в одном столбце, принимая весь столбец за 100%. Размер каждой категории определяется как вес в общей сумме категорий. (Рисунок 4)  Рисунок 4 Графики Графики используются для отражения колебаний и изменений данных во времени. (Рисунок 5)  Рисунок 5 Круговые диаграммы Используются для отображения наглядной структуры и пропорций. (Рисунок 6)  Рисунок 6 Настройка параметров 1. Элементы диаграммы Добавить подписи данных, название осей, изменить погрешность можно в меню управления элементами диаграммы. Просто ставьте галочку напротив нужных и снимайте с не нужных. (Рисунок 7)  Рисунок 7 Стили диаграмм Больше десятка различных стилей и множество цветовых гамм для графиков. Можно найти подходящее оформление в меню "Стили диаграмм". (Рисунок 8)  Рисунок 8 3.Фильтры отображаемых данных Скрыть ненужные данные из вида и изменить/расширить таблицу, на основе которой построен график, можно в меню "Фильтры диаграмм". (Рисунок 9)  Рисунок 9 Постройте все указанные виды диаграмм и настройте их параметры. Сохранить задание в файл с именем Фамилия_ПР_5_9.xlsx Задание 10. Сводные таблицы и расчеты.Исходные данные представлены на рисунке 1. Стоимость товара в партии рассчитывается умножением значений в полях: Количество*Цена.  Рисунок 1 С помощью следующего алгоритма мы детально рассмотрим пример, как построить сводную таблицу в Excel. На панели вверху окна переходим на вкладку «Вставка», где слева в углу выбираем «Сводная таблица». Далее на экране открывается диалоговое окошко, где требуется указать соответствующие параметры. На этом этапе создания сводных таблиц в Excel есть несколько важных моментов. Если перед тем, как начать формирование реестра, вы установили значок курсора на листе, то заполнение пустых строчек окна произойдёт автоматически. Иначе адрес диапазона данных нужно обозначить самостоятельно. (Рисунок 2)  Рисунок 2 Первую строку не оставляем пустой, иначе программа выдаст ошибку. Если есть источник, с которого планируете переноситься данные, то выберите его в пункте «Использовать внешний источник данных». Под внешним источником подразумевается другая книга Excel или набор моделей данных из СУБД. Выберите место, где будет располагаться будущая рамка с ячейками. Это может быть новое окно или же этот лист, рекомендуем использовать другой лист. Закрепив все настройки, получаем готовую основу. По левую сторону располагается область, где размещена основа будущей рамки. В правой части есть окно с настройками, которое помогает управлять реестром. (Рисунок 3)  Рисунок 3 Теперь необходимо разобраться, как строится вся эта конструкция. В окне настроек «Поля свободной таблицы», вы обозначаете данные, которые будут присутствовать. Вся структура строится таким образом, чтобы текстовые данные играли роль объединяющих элементов, а числовые показывали консолидированные значения. Например, объединим все поступления по отделам и узнаем на какую сумму получил товары каждый. Для этого поставим галочку у двух заголовков: отдел и стоимость товара в партии. (Рисунок 4)  Рисунок 4 Обратите внимание, как расположились эти данные в нижней области панели настройки. Отдел автоматически ушел в строки, а числовые данные в значения. Если попробовать щелкнуть по любому столбцу с числами, они будут появляться в этой области. А в самой таблице добавится новый столбец. (Рисунок 5)  Рисунок 5 При этом происходит суммирование. Сейчас мы можем узнать из нашего отчета, сколько товаров поступило в каждый отдел и их общую стоимость. Вы можете свободно корректировать ширину столбиков для оптимального расположения данных. Просто раздвигайте ширину столбцов или высоту строк так, как вы привыкли в Excel. Если вам не нравится такая ориентация, вы можете перетащить мышкой названия строк в область столбцов - просто наведите мышь, нажмите левую кнопку и тяните. (Рисунок 6)  Рисунок 6 Добавим поле «Цена» и найдем максимальную цену товара в каждом отделе. Фактически, узнаем сколько стоит самый дорогой. (выделить форматированием) Область «Фильтры» позволяет установить критерий отбора записей. Добавим поле «Дата поступления», просто поставив около него галочку. (Рисунок 7)  Рисунок 7 Сейчас сводная таблица в Excel выглядит неудобно, если нам необходимо провести анализ по дате. Поэтому переместим дату из строк в фильтры - просто перетяните. (Рисунок 8)  Рисунок 8 Итогом этих действий стало появление еще одного поля сверху. Чтобы выбрать дату, нажмем на стрелочку около слова «Все». Теперь нам доступен выбор конкретного дня, чтобы открыть список, щелкайте по треугольнику в правом углу. Также можно выбрать и значения для отдела. Выполните самостоятельно. Снимите галочки с тех, которые вас не интересуют, и вы получите только нужную информацию. Во время работы вы можете столкнуться с подобным сообщением «недопустимое имя сводной таблицы Excel». Это означает, что первая строка диапазона, откуда пытаются извлечь информацию, осталась с незаполненными ячейками. Чтобы решить эту проблему, вы должны заполнить пустоты колонки. (Рисунок 9)  Рисунок 10 Важным вопросом является то, как сделать и обновить сводную таблицу. Это актуально тогда, когда вы собираетесь добавить новые данные. Если обновление будет проходить только для одного столбца, то необходимо на любом её месте щелкнуть правой кнопкой мыши. В появившемся окне нужно нажать «Обновить». Измените цену сапог со 100 руб. до 1000 руб. и обновите сводную таблицу. Если же подобное действие необходимо провести сразу с несколькими столбцами и строками, то выделите любую зону и на верхней панели откройте вкладку «Анализ» и кликните на значок «Обновить». Дальше выбирайте желаемое действие. Если сводная таблица в Excel не нужна, то стоит разобраться, как её удалить. Выделите все составляющие вручную, или используя сочетание клавиш «CTRL+A». Далее нажмите клавишу «DELETE» и поле будет очищено. Чтобы добавить дополнительный столбец, вам необходимо добавить его в исходные данные и расширить диапазон для нашего реестра. Цена с НДС рассчитывается по формуле: Цена*1,18 (Рисунок 11)  Рисунок 11 Перейдите на вкладку «Анализ» и откройте источник данных. Excel сам все предложит. Обновите и вы получите новый перечень полей в области настройки. Добавить таблицу вы сможете только если «склеите» ее с исходной. Можно заменить диапазон в уже существующей, но добавить другой диапазон нельзя. Зато можно создать новую сводную таблицу на основе нескольких исходных, даже расположенных на разных листах. Указание: Как сделать сводную таблицу в Excel из нескольких листов Для этого нам понадобится мастер сводных таблиц. Добавим его на панель быстрого доступа (самый верх окна – слева). Нажмите выпадающую стрелочку и выберите «Другие команды». (Рисунок 12)  Рисунок 12 Выберите все команды. И найдите мастер сводных таблиц Excel, кликните по нему, затем на «Добавить» и ОК. Значок появится сверху. (Рисунок 13)  Рисунок 13 У вас должны быть две идентичные по полям таблицы на разных листах. Создайте на другом листе таблицу, аналогичную данной, но с датами за июнь. Заполните ее произвольными значениями цен и количества, оставив тот же ассортимент товаров. У нас получились данные о поступлениях в отделы за май и июнь. Нажимайте на ярлык мастера сводных таблиц и выбирайте консолидацию диапазонов. (Рисунок 14)  Рисунок 14 Нам нужно несколько полей, а не одно. На следующем этапе выделите первый диапазон и нажмите кнопку «Добавить». Затем переключитесь на другой лист (щелкните по его названию внизу) и снова «Добавить». У вас будут созданы два диапазона. (Рисунок 15)  Рисунок 15 Не стоит выделять всю таблицу целиком. Нам нужна информация о поступлениях в отделы, поэтому мы выделили диапазон, начиная со столбца «Отдел». Дайте имя каждому. Кликайте кружочек 1, затем в поле вписывайте «май», кликайте кружочек 2 и вписывайте в поле 2 «июнь». Не забывайте менять диапазоны в области. Должен быть выделен тот, который именуем. (Рисунок 16)  Рисунок 16 Щелкайте «Далее» и создавайте на новом листе. (Рисунок 17)  Рисунок 17 После нажатия на «Готово» получим результат. (Рисунок 18)  Рисунок 18 Создайте отчет по полученной сводной таблице. Установив курсор на любой ячейке, переходим во вкладку «Конструктор», а следом «Макет отчета». (Рисунок 19)  Рисунок 19 Вам откроются на выбор три типа для структуризации информации: Сжатая форма Такой тип программа применяется автоматически. Данные не растягиваются, поэтому прокручивать изображения практически не нужно. Можно сэкономить место на подписях и оставить его для чисел. Структурированная форма Все показатели подаются иерархично: от малого к большому. Табличная форма Информация представляется под видом реестра. Это позволяет легко переносить ячейки на новые листы. Остановив выбор на подходящем макете, вы закрепляете внесенные коррективы. Сохранить задание в файл с именем Фамилия_ПР_5_8.xlsx |