Лаб.практикум по инф-ке_МУ. С. Л. Миньков лабораторный практикум по информатике
 Скачать 4.64 Mb.
|
|
Раздел 3. Обработка данных Цель раздела:познакомиться со способами сортировки и фильтрации данных в Excel, научиться создавать сводные таб- лицы, изучить способы консолидации данных. Область электронной таблицы можно рассматривать как ба- зу данных. При этом столбцы называются полями, а строки — записями. Столбцам присваиваются имена, которые будут ис- пользоваться как имена полей записей. При формировании структуры базы данных необходимо учитывать следующие ограничения: – первый ряд базы данных должен содержать неповторяю- щиеся имена полей и располагаться в одной строке; – для имен полей следует использовать шрифт, тип данных, формат, рамку, отличные от тех, которые используются для дан- ных в записях; – таблицу следует отделить от других данных рабочего ли- ста пустым столбцом и пустой строкой; – информация по полям должна быть однородной, т. е. толь- ко цифры или только текст. Работа с любой базой данных заключается в поиске инфор- мации по определенным критериям, перегруппировке и обработ- ке информации. Примечание. Прежде чем начинать работу с базой данных, необходимо «встать в таблицу», т. е. щелкнуть мышью по какой- либо ее ячейке (но не выделять отдельные столбцы!). 3.1 Сортировка и фильтрация Поместить на листе Excel табл. 3.1 и, используя ее данные, создать на этом же листе новую (рабочую) таблицу с относитель- ными величинами, разделив все параметры на соответствующий параметр Земли. Не забудьте изменить названия полей в новой таблице! Для проведения дальнейших операций с полученной табли- цей необходимо, чтобы в ее ячейках находились числовые значе- ния, а не формулы, их вычисляющие 1 . Замена производится с по- мощью опции Специальная вставка (см. рис. 2.2). 1 Можно использовать и формулы, но тогда адреса всех ячеек, встре- чающихся в них, должны иметь абсолютную адресацию. 54 Таблица 3.1 Планета Период обращения, земной год Расстояние от Солнца, млн км Диаметр, тыс. км Масса, Тт Количество спутников Меркурий 0,241 58 4,9 0,32 10 9 0 Венера 0,615 108 12,1 4,86 10 9 0 Земля 1 150 12,8 6,0 10 9 1 Марс 1,881 288 6,8 6,1 10 8 2 Юпитер 11,86 778 142,6 19,07 10 11 16 Сатурн 29,46 1426 120,2 57,09 10 10 17 Уран 84,01 2869 49,0 87,24 10 9 14 Нептун 164,8 4496 50,2 10,34 10 10 2 Плутон 247,7 5900 2,8 0,1 10 9 1 Задание 3.1. Сортировка В полученной таблице, используя вкладку Сортировка и фильтр ленты Главная или ленты Данные (рис. 3.1), выпол- нить следующие операции: 1) отсортировать данные в порядке убывания количества спутников; 2) отсортировать данные в алфавитном порядке названий планет; 3) отсортировать данные в порядке возрастания массы. Разместить все результаты сортировок на одном листе рабо- чей книги. а Рис. 3.1 — Вкладки Сортировка и фильтр ленты Главная (а) и ленты Данные (б) б 55 Задание 3.2. Фильтрация Фильтрация позволяет быстро сделать выборку из таблицы, используя различные критерии выборки. В Excel эту операцию можно выполнять различными спосо- бами. Способ 1. Фильтр Вызов Фильтра осуществляется через вкладку Сортировка и фильтр ленты Главная или ленты Данные кнопкой с изобра- жением воронки(рис. 3.1). При этом в заголовках таблицы появ- ляются кнопки с треугольниками. При нажатии на них появляет- ся меню с условиями отбора автофильтра (рис. 3.2) для выбран- ного столбца. Рис. 3.2 — Условия фильтрации Щелчок по условию вызывает появление пользовательского автофильтра (рис. 3.3), где задаются критериальные значени. 56 Рис. 3.3 — Диалоговое окно Пользовательский автофильтр В левом поле этого окна под надписью Показать только те строки, значения которых, выбирается необходимый оператор сравнения (равно, меньше, больше и т. п.), а в правом поле зада- ется значение сравнения. Нижние поля используются для второго условия отбора, соединяющегося с первым знаками логических операций И или ИЛИ. После работы автофильтра в таблице оказываются только те записи, которые удовлетворяют критериям фильтрации. Параметр Первые 10… определяет строки с максимальным или минимальным значением ячеек текущего столбца. Эта строка открывает диалоговое окно Наложение условия по списку (рис. 3.4). Рис. 3.4 — Диалоговое окно Наложение условия по списку Задание 3.3. Автофильтр С помощью Автофильтра найти: 1) планеты, имеющие диаметр менее 4-х диаметров Земли и период обращения более 80 земных лет; 57 2) планеты, находящиеся от Солнца на расстоянии не менее 0,5 расстояния от Земли, имеющие массу от одной до 100 масс Земли и не более 2-х спутников; 3) три планеты, имеющие самый большой диаметр. Разместить результаты фильтрации на различных листах ра- бочей книги. Способ 2. Расширенный фильтр В отличие от Автофильтра Расширенный фильтр (рис. 3.5)позволяет проводить фильтрацию не только по месту расположения таблицы, но и выносить результат в другое место листа. Чтобы выполнить фильтрацию, необходимо воспользовать- ся командами ленты Данные | группа Сортировка и фильтр | Дополнительно. В открывшемся диалоговом окне указать адреса трех диапазонов, которые предварительно должны быть опре- делены. Первый диапазон — это исходная таблица (область базы данных). Пусть, например, она располагается в ячейках В2:G11. Эта область указывается в поле Исходный диапазон (рис. 3.5). Рис. 3.5 — Диалоговое окно Расширенный фильтр Второй диапазон — это область, где задаются критерии фильтрации. Его формируют из заголовков столбцов таблицы (т. е. имен полей базы данных), по которым ведется отбор, 58 и строк, где в соответствующих ячейках записываются условия фильтрации. Эта область указывается в поле Диапазон условий. Если все условия отбора разместить в одной строке сразу под заголовками, то этим самым реализуется их взаимодействие по схеме «И» (требуется одновременное их выполнение): Заголовок 1 Заголовок 2 Заголовок 3 >=5 <110 =2 Если по одному полю необходимо задать два условия отбо- ра («двойное неравенство»), то они располагаются рядом, а в сле- дующей строке записываются ограничение снизу и ограничение сверху: Заголовок 1 Заголовок 1 >=5 <=10 Для соединения условий отбора по схеме «ИЛИ» необходи- мо каждое из них разместить в отдельной строке: Заголовок1 Заголовок 2 Заголовок 3 >=5 <110 =2 Для того чтобы найти записи, отвечающие одному из двух наборов условий, каждый из которых содержит условия более чем для одного столбца, необходимо ввести эти условия отбора в отдельные строки: Заголовок 1 Заголовок 2 =5 >110 =8 <=1000 Третий диапазон нужен, если результат фильтрации соби- раются расположить не на месте исходного диапазона, а в другом месте. Для этого достаточно указать в поле Поместить резуль- тат в диапазон адрес только одной ячейки. Она станет левой верхней ячейкой диапазона результатов. 59 Все три диапазона на листе должны быть отделены друг от друга хотя бы одной пустой строкой или столбцом. Опция Только уникальные записи позволяет исключить из таблицы все повторяющиеся строки, оставив только уникаль- ные. Чтобы восстановить таблицу после работы Автофильтра или Расширенного фильтра, следует снова щелкнуть по значку воронки. Задание 3.4. Расширенный фильтр Спомощью Расширенного фильтра найти: 1) планеты с периодом обращения от 10 до 100 земных лет и количеством спутников не более 15; 2) планеты, у которых либо диаметр не менее 4-х земных, либо масса более 100 земных масс; 3) среди планет без спутников тех, которые находятся от Солнца на расстоянии менее половины земного, а среди пла- нет с не менее чем 14-ю спутниками тех, которые находятся от Солнца не ближе чем 10 земных расстояний. Результаты поместить на одном листе в последовательности: исходная таблица, условия, результат, условия, результат и т. д. 3.2 Сводные таблицы Сводная таблица — это еще один инструмент Excel для об- работки больших списков с данными, позволяющий подводить итоги, выполнять сортировку и фильтрацию списков. Построение сводной таблицы (Вставка | группа Таблицы | Сводная таблица) осуществляется в несколько шагов. Сначала указывается источник данных и указывается, где поместить свод- ную таблицу (рис. 3.6). Если было указано На новый лист, то при нажатии ОК открывается новый лист, если На существу- ющий лист, то для указания места размещения достаточно задать адрес левого верхнего угла сводной таблицы. При этом в правой части листа появляются элементы управ- ления сводной таблицей (рис. 3.7). 60 Рис. 3.6 — Начало создания сводной таблицы В окне Список полей… (рис. 3.7, а) отмечаем те поля, ко- торые должны быть отражены в сводной таблице. В окно Фильтр перетаскиваем из предыдущего окна те поля, по кото- рым должна проводиться фильтрация сводной таблицы. Названия строк и столбцов сводной таблицы задаются в соответствующих окнах (если значения из окна Названия столбцов перенести в Название строк, получим вертикальное расположение сводной таблицы). а б в Рис. 3.7 — Параметры сводной таблицы: а — выбор полей; б — итоги и фильтры; в — меню поля в окне Значения 61 В последнем окне Значения задают те поля, по которым нужно провести нужную операцию сведения данных. По умолча- нию это суммирование. Изменить операцию можно щелчком мыши по соответствующему полю. В появившемся меню (рис. 3.7, в) выбрать Параметры полей значений (рис. 3.8) и за- дать нужную операцию. Рис. 3.8 — Параметры поля значений Задание 3.5. Создание сводной таблицы Заполнить табл. 3.2, занеся недостающие данные и рассчитав процент удержания с начисленной суммы по следующему правилу: при количестве иждивенцев более трех — 0 %, при трех — 5 %, при двух — 10 %, при одном — 12 %, если нет — 14 %. Расчет оформить с помощью вложенных функций ЕСЛИ. На основе построенной таблицы создать сводную таблицу, найдя итоги (суммы) по полям Всего начислено, Всего удержа- но, Сумма к выдаче по каждой фамилии, расположив фамилии в алфавитном порядке, и провести фильтрацию отдельно по кате- гории Количество иждивенцев и по категории Отдел. Поме- стить сводную таблицу на отдельном листе. Постарайтесь создать 62 компактную и наглядную сводную таблицу, избежав излишнего текста и пустых ячеек. На основе сводной таблицы сформировать отчет по лицам, работающим в третьем отделе и имеющим по одному иждивенцу. Таблица 3.2 Ф.И.О. Отдел Количество иждивенцев Всего начислено, руб. Процент удержания Всего удержано, руб. Сумма к вы- даче, руб. 1. Петухова К.И. 1 1 1260 2. Безенчук П.Ф. 3 2 1100 3. Воробьянинов И.М. 2 3 800 4. Востриков Ф.О. 2 2 750 5. Коробейников В.А. 3 1 715 6. Грицацуева В.С. 1 3 630 7. Гаврилин З.С. 1 4 620 8. Треухов Т.И. 3 1 560 9. Изнуренков А.В. 1 0 420 10. Щукина Э.Е. 3 1 250 3.3 Промежуточные итоги Промежуточные итоги по различным группам можно под- водить и без создания сводных таблиц, используя команду Excel Промежуточный итог. Эту команду можно применить к таблице, если она не под- вергалась форматированию с помощью команды Форматиро- вать как таблицу из вкладки Стили ленты Главная. Необходи- мо сначала вернуть ее в обычный диапазон данных. Для этого, выделив таблицу, нужно выполнить команду: Работа с таблица- ми | Конструктор | Преобразовать в диапазон. Сначала таблица сортируется по столбцу, по которому необходимо подвести промежуточные итоги. Затем выбирается команда Данные | группа Структура | Промежуточный итог. В появившемся диалоговом окне (рис. 3.9) в поле При каждом изменении в: задается классификационная категория; в поле Операция: задается функция, необходимая для подведения ито- гов; в поле Добавить итоги по: выделяются названия столбцов, где находятся итожимые данные. 63 Рис. 3.9 — Диалоговое окно Промежуточные итоги Для отображения только промежуточных и общих итогов используйте обозначения уровней структуры рядом с номерами строк. Кнопки + и – позволяют отобразить и скрыть строки по- дробных данных для отдельных итогов. Задание 3.6. Получение промежуточных итогов На таблице 3.2 получить промежуточные итоги во всех от- делах по позициям Количество иждивенцев, Всего начислено, Всего удержано и Сумма к выдаче. Изучить и описать структу- ру полученной таблицы. 3.4 Связь таблиц (консолидация данных) Excel позволяет связывать ячейки таблиц, находящихся на различных рабочих листах одного файла, в разных файлах, на различных логических дисках таким образом, чтобы в резуль- тате изменения значения ячейки в одной таблице изменялось бы содержание другой. 64 Связь табличных данных можно осуществлять различными способами. Способ 1. Формула связи. Связь между листами можно за- дать путем использования в ячейке какого-либо листа формулы связи со ссылкой на ячейку другого листа, например: =НазвЛиста!АдрЯч. Связь между файлами можно задать путем использования в ячейке листа какого-либо файла Excel формулы связи со ссыл- кой на ячейку в другом файле Excel, например: ='[ИмяФайла.xls]НазвЛиста'!АдрЯч. Путь заключен в одинарные кавычки, имя файла заключено в квадратные скобки. Если файлы находятся в одном каталоге, то формула немного упрощается: =[ИмяФайла]НазвЛиста!АдрЯч. Если файлы Excel находятся на разных логических дисках, то формула связи выглядит следующим образом: ='A:\Папка\[ИмяФайла.xls] НазвЛиста'!АдрЯч. Используя формулы связи, можно в ячейках итоговой таб- лицы совершать любые операции над ячейками исходных таблиц. Для упрощения набора таких формул рекомендуется задавать ад- рес нужной ячейки не набором его с клавиатуры, а щелчком ука- зателя мыши в соответствующем месте исходной таблицы, вы- брав нужный путь Файл | Открыть и т. д. Способ 2. Консолидация данных. Консолидация позволяет объединять данные из областей-источников и выводить их в об- ласти назначения. При этом могут использоваться различные функции: суммирования, расчета среднего арифметического, подсчетов минимальных и максимальных значений и т. д. Для проведения консолидации необходимо выбрать Данные | группа Работа с данными | Консолидация и в поле Ссылка (рис. 3.10) определить по порядку области-источники (массивы ячеек), данные из которых нужно консолидировать, добавляя их в поле Список диапазонов. Не забудьте поставить флажок напротив опции Создавать связи с исходными данными! Для переноса текстовой информации (имена строк и столб- цов) используйте опцию Использовать в качестве имен. 65 Рис. 3.10 — Диалоговое окно Консолидация Задание 3.7. Формула связи Занести табл. 3.3 на три листа (не забудьте о возможности, описанной в п. 1.4!). Рассчитать выручку и изменить название листов на Январь, Февраль, Март. Подкорректировать заголов- ки таблиц и изменить данные второго и третьего столбцов (по вашему усмотрению). Таблица 3.3 Наименование продукции Исходные данные за январь Цена в USD Продано Выручка Телевизоры 350 20 Ноутбуки 520 35 Планшетные ПК 750 65 Цифровые камеры 250 30 Электронные книги 150 15 Смартфоны 400 27 Медиаплейеры 170 11 Blu-ray плейеры 110 14 Итого: На четвертом листе (Квартал. Способ 1) создать таблицу итоговых показателей (Продано и Выручка за квартал) по все- му ассортименту продукции за квартал и заполнить эту таблицу, 66 суммируя данные, находящиеся в соответствующих ячейках ли- стов показателей за январь-март. На пятом листе (Квартал. Способ 2) получить аналогичную таблицу, но с помощью консолидации. Изучить структуру полученной консолидированной табли- цы. Она содержит скрытые строки, в которых находится инфор- мация из других листов. Скрытые строки можно открывать и снова скрывать, нажимая соответственно кнопки «+» и «–». Проверьте, изменяются ли значения консолидированной таблицы при изменениях в исходных таблицах. Сравнить оба способа. Задание 3.8. Консолидация Выбрав данные из табл. 3.4, разнести их поквартально по разным листам одного файла. Определить среднюю цену про- изводителей по каждой позиции в каждом квартале (в тыс. руб.) и соотношение цен на отдельные виды энергоресурсов с ценой на нефть (в процентах). В другом файле с помощью консолида- ции сформировать таблицу со среднегодовыми данными по це- нам и соотношению цен. Таблица 3.4 Период (1997 г.) Цены, тыс. руб. за тонну Неф ть Бензин ав- томобиль- ный Топливо дизельное Мазут топочный Газ естествен- ный * Уголь энерге- тический для кок- сования январь февраль март апрель май июнь июль август сентябрь октябрь ноябрь декабрь 370 373 376 371 372 375 375 375 376 384 379 353 933 886 907 932 949 968 968 978 1011 1007 1001 1054 943 944 947 970 980 984 981 981 1013 1012 1011 985 443 433 434 440 437 436 441 440 440 440 445 438 37,2 39,0 39,0 39,0 39,3 39,3 39,5 39,6 39,4 40,2 40,3 44,6 144 143 146 141 141 141 139 137 138 140 148 146 133 130 138 129 136 129 127 128 124 125 122 123 * Цена за 1000 м 3 67 3.5 Форма данных Форма данных предоставляет удобный способ ввода или отображения одной строки данных из диапазона или таблицы, не требующий применения горизонтальной прокрутки. Кнопки Форма нет на ленте, но в Excel 2010 кнопку Форма можно добавить на панель быстрого доступа. Для этого необхо- димо: 1) щелкнуть стрелку, расположенную рядом с панелью быстрого доступа (левый верхний угол листа), и выбрать пункт Другие команды; 2) в поле Выбрать команды из выбрать пункт Все команды; 3) в списке выбрать кнопку Форма и нажать Добавить. Форма данных обычно используется для перемещения меж- ду столбцами, которых слишком много для одновременного отображения на экране. Форму данных можно применять, когда достаточно простой формы с текстовыми полями, в качестве подписей которых используются заголовки столбцов, и когда не требуются сложные или пользовательские возможности, например списки и счетчики. Рис. 3.11 — Форма, созданная на основе таблицы (рис. 1.1). Строка заголовков преобразована в одну строку листа Excel Excel автоматически создает встроенные формы данных для диапазона или таблицы. Такая форма представляет собой диало- говое окно, в котором все заголовки столбцов отображаются 68 в виде подписей. Каждой подписи соответствует текстовое поле, в которое можно вводить данные для столбца (максимальное ко- личество столбцов — 32). В форме данных можно вводить новые строки, искать строки с помощью навигации, а также (в зависи- мости от содержимого ячеек) обновлять строки или удалять их. Если ячейка содержит формулу, то в форме данных отображается только результат ее вычисления. Саму формулу невозможно из- менить с помощью формы данных. Задание 3.9. Создание формы данных Создать форму данных для таблицы 3.1. |