пр. Создание и сортировка списков. Подведение итогов Тема 1 Создание и сортировка списков
 Скачать 0.75 Mb.
|
|
Создание и сортировка списков. Подведение итогов Тема 1 Создание и сортировка списков 1.1 Основные понятия Список в Excel – это табличные данные, описывающие некоторую предметную область, например, список студентов, содержащий фамилии, группы и адреса, сортамент прокатных профилей, содержащий геометрические и инерционные характеристики и т.д. Строки в таких списках описывают один экземпляр предметной области, например, отдельного студента, и называются записями. Столбцы описывают отдельную характеристику объекта, например, площадь профиля, и называются полями. Пример.Фрагмент из ГОСТ 8239-89 «Двутавры стальные горячекатанные» Номер двутавра Размеры Площадь поперечного сечения, см 2 Масса 1 м, кг Справочные значения для осей h b s t R r X – X Y – Y не б олее I x , см 4 W x , см 3 i x , см S x , см 3 I y , см 4 W y , см 3 i y , см мм 10 100 55 4,5 7,2 7,0 2,5 12,0 9,46 198 39,7 4,06 23,0 17,9 6,49 1,22 12 120 64 4,8 7,3 7,5 3,0 14,7 11,50 350 58,4 4,88 33,7 27,9 8,72 1,38 14 140 73 4,9 7,5 8,0 3,0 17,4 13,70 572 81,7 5,73 46,8 41,9 11,50 1,55 16 160 81 5,0 7,8 8,5 3,5 20,2 15,90 873 109,0 6,57 62,3 58,6 14,50 1,70 18 180 90 5,1 8,1 9,0 3,5 23,4 18,40 1290 143,0 7,42 81,4 82,6 18,40 1,88 20 200 100 5,2 8,4 9,5 4,0 26,8 21,00 1840 184,0 8,28 104,0 115,0 23,10 2,07 Пример. Фрагмент списка "Студенты" № зач. книжки Группа Фамилия Имя Пол Дата рождения Возр аст Индекс Год оконч. школы Населенный пункт Телефон 29093961001 ПГС-61 Коряко Татьяна ж 09.07.1978 26 658040 1995 Новоалтайск (8-232)6-92-88 29093963008 ПГС-63 Янин Евгений м 11.11.1978 26 658040 1996 Новоалтайск (8-232)2-25-08 29091962003 ПСК-62 Мирко Алеся ж 22.11.1978 26 658640 1996 Романово 2-29-85 29091963001 ПСК-62 Верещагина Екатерина ж 24.11.1978 26 659630 1995 Алтайский р.п. 21-5-95 29093961014 ПГС-61 Путилин Александр м 03.12.1978 26 653622 1996 Фунтики, Топчихинск.р (8-252)2-75-01 Команды для работы со списками в Excel находятся на ленте на закладке Данные. Если в списке много записей, то при прокрутке списка названия полей не будут видны на экране. Желательно постоянно иметь на экране названия полей, т.е. закрепить их. Закрепление выполняется следующим образом: Названия полей Записи Значения полей 1. Установить курсор так, чтобы закрепляемые строки и столбцы оказались выше и левее ячейки. 2. Выполнить команду Вид - Окно – Закрепить области. Для отмены закрепления выполняется команда меню Окно – Снять закрепление. 1.2 Рекомендации по созданию списков При создании списков в Excel необходимо придерживаться следующих правил: 1. Первая строка списка оформляется как заголовок, содержащий названия (подписи) полей. Каждое название должно занимать только один столбец и иметь текстовый формат. Excel использует подписи столбцов при создании отчетов, поиске и оформлении данных. 2. Желательно выделить заголовок либо шрифтом, либо границей, но не пустой строкой. 3. В каждом столбце информация должна быть однородной, например, числа, даты, текст и т.д. 4. В текстовых столбцах не рекомендуется использовать начальные и конечные пробелы, т.к. в дальнейшем это может повлиять на результат поиска и сортировки. 5. Список необходимо отделять от остальной информации, размещенной на этом же листе, пустой строкой и пустым столбцом. Это позволяет Excel автоматически обнаружить и выделить список при выполнении сортировки, наложении фильтра или вставке вычисляемых автоматически итоговых значений. 6. Внутри списка не должно быть полностью пустых строк и столбцов. 7. Желательно размещать разные списки на разных листах рабочей книги, т.к. некоторые возможности управления списком, например, фильтрация, могут быть использованы только для одного списка. 1.3 Проверка данных при вводе в список Если элементы списка должны удовлетворять некоторым условиям, то при заполнении списка можно проверять вводимые значения, выполнив следующие действия. 1. Выделить столбец, щелкнув по его имени. 2. Меню Данные – Работа с данными - Проверка. При этом появляется диалоговое окно Проверка вводимых значений. 3. В диалоговом окне: a) задать условие на вкладке . Условие зависит от типа значений в столбце - для числовых значений и дат в списке "Значение:" задается операция сравнения (между, вне, равно, не равно, больше, меньше, больше или равно, меньше или равно), а в строках "Минимум:"(или "Максимум:") значение, с которым выполняется сравнение. Сравниваемое значение можно задавать константой, адресом ячейки или формулой. - для текстовых данных может быть введено ограничение на длину строки - если элементы поля принимают ограниченный набор значений, то выбирается Тип данных – Список, а значения поля либо перечисляются в строке Источник, либо задаются на рабочем листе, а в строку Источник вводится адрес диапазона. Включенный флажок Список допустимых значений означает, что при вводе значений в ячейку появляется символ , и значения можно выбирать из раскрывающегося списка. b) задать сообщение об ошибке, которое будет появляться при вводе неверного значения в ячейку. Сообщение появляется в диалоговом окне. c) задать, при необходимости, всплывающее сообщение, которое появляется при установке курсора на ячейку Пример 1. В списке студентов задать следующие ограничения: 1) Для поля Номер зач. книжки задать длину строки не менее 11 символов a) выделить столбец А, содержащий номера зачетных книжек, b) меню Данные - Проверка c) задать параметры d) задать сообщение об ошибке 2) Для поля Группа выполнять выбор из списка a) занести в ячейки N1:N10 названия групп ПГС-01, ПГС-03, ПГС-04, ПГС-11, ПГС-61, ПГС-62, ПГС-63, ПГС-71, ПСК-61, ПСК-62, b) выделить столбец В, содержащий группу, c) меню Данные – Проверка, d) задать параметры e) задать сообщение об ошибке 3) Для поля Дата рождения задать условие – ввод дат после 1976 года a) выделить столбец G, содержащий даты рождения, b) меню Данные – Проверка, c) задать параметры d) задать сообщение об ошибке 1.4 Сортировка списков Сортировка – это переупорядочивание записей в списке по значениям одного или нескольких полей. 1.4.1 Сортировка по одному полю 1. Установить курсор на любую ячейку поля (столбца), по которому требуется выполнить сортировку ( столбец не выделять! ). 2. Нажать на стандартной панели инструментов: a) кнопку для сортировки по возрастанию, b) кнопку для сортировки по убыванию. Замечание: Можно также выполнить команду меню Данные – Сортировка. Сортировка по возрастанию зависит от типа поля: 1. Числовые значения упорядочиваются от наименьших отрицательных до наибольших положительных. 2. Дата и время упорядочиваются от ранних значений до поздних. 3. Текстовые значения упорядочиваются по алфавиту. 4. Пустые значения в сортировке не участвуют, они располагаются в конце списка. Сортировка по убыванию расставляет значения в обратном порядке. 1.4.2 Сортировка по нескольким полям (многоуровневая сортировка) 1. Установить курсор в любую ячейку списка. 2. Меню Данные – Сортировка. При этом появляется диалоговое окно, в котором задаются следующие параметры: - в столбце «Сортировать по» выбрать название поля, по которому выполняется сортировка, - в стобце «Порядок» указать направление сортировки - щелкнуть по кнопе «Добавить уровень» - выбрать поле сортировки 2-го уровня и указать порядок Отсортированные по нескольким полям данные располагаются следующим образом: 1. Сначала создаются группы с одинаковыми значениями поля, заданного в списке "Сортировать по". Группы упорядочиваются (по возрастанию или убыванию). 2. Внутри каждой группы создаются подгруппы с одинаковыми значениями поля, заданного в списке "Затем по". Подгруппы упорядочиваются, не изменяя сортировки внешней группы. Замечание: Таким образом, после сортировки список представляет собой упорядоченный набор записей, сгруппированных по значению одного или нескольких полей. Пример 1. Отсортировать список студентов по полю Группа по убыванию 1. Установить курсор в любую ячейку столбца Группа. 2. На панели инструментов нажать кнопку . Номер зач. книжки Группа Фамилия Имя Пол Дата рождения Возраст Индекс Год оконч. школы Населенный пункт Телефон 1- я гр уп па 29091962004 ПСК-62 Процюк Оксана ж 15.10.1977 27 490025 1996 Семипалатинск 29091962003 ПСК-62 Мирко Алеся ж 22.11.1978 26 658640 1996 Романово 2-29-85 29091963001 ПСК-62 Верещагина Екатерина ж 24.11.1978 26 659630 1995 Алтайский р.п. 21-5-95 … … … … … 2- я гр уп па 29091961004 ПСК-61 Шмаков Дмитрий м 03.02.1979 25 656060 1996 Барнаул 29091961001 ПСК-61 Шнайдер Александр м 08.04.1979 25 656062 1996 Барнаул 54-04-53 29091961003 ПСК-61 Пропп Владимир м 28.12.1979 25 658862 1996 Табунский р-н … … … … … 3- я гр уп па 29093971001 ПГС-71 Соколов Александр м 11.06.1976 28 656906 1993 Барнаул 29093971002 ПГС-71 Панюшкин Евгений м 18.02.1979 25 658041 1996 Белоярск 29093971003 ПГС-71 Богданов Евгений м 16.04.1979 25 656044 1996 Барнаул 52-02-08 … … … … … Тема 2 Создание и сортировка списков Итоги подразумевают выполнение некоторой итоговой операции (суммы, среднего значения, подсчет количества и др.) над значениями некоторого поля для группы записей. Для подведения итогов необходимо определить: 1. По какому полю (полям) выполнять группировку (сортировку). 2. Над значениями какого поля (полей) выполнять итоговые операции. 3. Какие итоговые операции следует применить. Для вычисления итогов используется команда меню Данные – Структура – Промежуточный Итог. Excel позволяет автоматически выполнить подведение итогов двух видов: 1. простых промежуточных итогов (список предварительно сортируется по одному полю), 2. вложенных промежуточных итогов (список предварительно сортируется по нескольким полям). 2.1 Вычисление простых итогов с использованием одной операции для различных полей 1. Выполнить сортировку по тому полю, значения которого должны быть объединены в группу. 2. Установить курсор в любую ячейку списка. 3. Меню Данные – Итоги. Заполнить следующие элементы диалогового окна, указанные на рисунке 1 : Рисунок 1 – Подведение итогов Пример а) выбрать из списка имя поля, по которому выполнялась сортировка (группировка) б) выбрать из списка итоговую операцию в) включить флажки для полей, над которыми будет выполнена итоговая операция г) включить флажок для замены текущих итогов новыми д) включить флажок, если необходимо итоговые значения разместить под данными. Основные итоговые операции приведены в таблице 1. Таблица 1 – Основные итоговые операции Операция Результат Пример Сумма Сумма чисел. Эта операция используется по умолчанию для подведения итогов по числовым полям. Возраст 26 25 Сумма 51 Среднее Среднее для числовых значений в списке. Среднее 25.5 Максимум Максимальное число. Максимум 26 Минимум Минимальное число. Минимум 25 Произведение Результат произведения всех числовых значений в списке. Произведение 650 Количество чисел Количество записей или строк, содержащих числа. Фамилия Возраст Иванов 26 Сидоров 25 Кол-во 0 2 Количество значений Количество записей или строк данных. Эта операция используется по умолчанию для подведения итогов по нечисловым полям. Фамилия Возраст Иванов 26 Сидоров 25 Кол-во 2 2 Пример. Подсчитать максимальную цену в каждом типе изделия. 1. Выполнить сортировку по полю Тип изделия. 2. Данные –Структура _промежуточный итог 3. Применяемую операцию можно указать для нескольких полей 2.2 Вычисление простых итогов с использованием различных операций для полей 1. Выполнить сортировку по тому полю, значения которого должны быть объединены в группу. 2. Установить курсор в любую ячейку списка. 3. Выполнить итоги столько раз, сколько дано итоговых операций, задавая одно и то же значение в списке и разные итоговые операции, при 2-ом и далее подведении итогов отключить флажок . 2.3 Вычисление вложенных итогов 1. Выполнить многоуровневую сортировку по тем полям, значения которых должны быть сгруппированы. 2. Установить курсор в любую ячейку списка. 3. Выполнить итоги столько раз, сколько задано операций для внешней группы записей. 4. Выполнить итоги столько раз, сколько задано операций для вложенной группы записей и т.д. При каждом повторном подведении итогов отключать флажок . Пример. Подсчитать количество типов, добавляя количество фирм по каждому городу. 1. Выполнить многоуровневую сортировку по полям Тип и Город 2. Найти количество для каждого типа 3. Найти количество для каждой группы 2.3.1 Работа со структурой итогов После выполнения команды отображаются промежуточные итоги для каждой группы записей и общие итоги для всех записей. Таблица переходит в режим структуры, т. е. слева появляются символы структуры. Чтобы удалить символы структуры в промежуточных итогах, необходимо выполнить команду меню Данные – Структура – Удалить структуру. Используя символы структуры, можно отображать выборочные промежуточные итоги. Описание символов структуры приведено в таблицах 2,3. Таблица 2 – Разворачивание итогов Чтобы просмотреть Действие детальные данные внутри группы кнопку определенный уровень структуры символ уровня строки или столбца все детальные данные в структуре символ самого нижнего уровня строки или столбца. Например, если существует шесть уровней, то нажмите кнопку Таблица 3 – Сворачивание итогов Чтобы скрыть Действие детальные данные внутри группы панель уровня строки Щелкните мышью в любом месте на панели уровня или символ скрытия детальных данных определенный уровень структуры символ предыдущего уровня строки . Например, если в структуре шесть уровней, то скрыть шестой можно, нажав на кнопку . все детальные данные в структуре символ первого уровня 2.3.2 Удаление итогов Для того, чтобы отменить итоги и восстановить список, используется команда меню Данные – Промежуточные Итоги, командная кнопка диалогового окна Промежуточные итоги. Пр ом е жу точ н ые и тоги Общий итог Символы структуры |