Конспект лекций по Excell. 2011 печ. 184Л Электронные таблицы.Кон.лек- 26.12. Конспект лекций по дисциплине компьютернаятехникаипрограммировани е для студентов 1го курса дневной и заочной форм обучения образовательно
 Скачать 3.95 Mb.
|
|
м) не содержит – проверка на то, что текстовое значение в поле не содержит указанный фрагмент. Правый же список содержит все неповторяющиеся значения данного поля, с которыми, собственно, и будет выполняться сравнения. В него также можно ввести и любое другое значение – не обязательно из списка. • Неповторяющиеся значения данного поля – отображаются все за- писи с выбранным значением в поле. • Пустые – выводятся только строки с пустым значением данного поля. • Непустые – выводятся только строки с не пустым значением данного поля. После завершения формирования условий фильтрации, в тех полях, по которым были установлены такие условия, цвет стрелки на кнопке в строке заголовка изменится с черного на синий Отключение (деактивизация) Автофильтра выполняется по команде Данные → Фильтр → Автофильтр путем сброса соответствующего флажка. В результате: а) строка заголовка указанного диапазона вернется в исходное состояние – из нее будут удалены все кнопки раскрывающихся списков, и б) отображены все строки базы данных, которые ранее были скрыты при по- мощи Автофильтра. Расширенный фильтр служит для формирования сколь угодно слож- ных критериев поиска информации в базах данных в Microsoft Excel. Основной 147 его особенностью является необходимость во вспомогательном списке – диа- пазоне критериев, в котором, собственно, и формируются все условия отбора записей из базы данных. Это несколько усложняет процесс формирования критериев отбора записей, но зато позволяет указывать такие условия, кото- рые недоступны ни при использовании Формы ввода данных, ни Автофильтра. Расширенный фильтр MExcel применяется по следующему алгоритму: 1. Сформировать критерий поиска информации в диапазоне критериев. 2. Выполнить команду Данные → Фильтр → Расширенный фильтр. 3. В раскрывшемся окне диалога Расширенный фильтр: а) С помощью переключателя Обработка – выбрать место, куда будут по- мещены результаты фильтрации: • фильтровать список на месте – чтобы скрыть те записи базы дан- ных, которые не отвечают критерию поиска. При этом также как и в случае с Автофильтром, скрываются целые строки рабочего листа, и данные, расположенные слева или справа от списка, могут стать недоступными. • скопировать результат в другое место – для записи результата фильтрации в другое место. б) Указать в полях редактирования диапазоны: • Исходный диапазон – диапазон базы данных, • Диапазон условий – диапазон критериев, и • Поместить результат в диапазон – диапазон извлеченных значений. Оно доступно только если переключатель Обработка установлен в положение скопировать результат в другое место. Значения этих полей можно ввести либо непосредственно с клавиатуры, либо с помощью левой кнопки мыши – методом прямого указания. 148 Расширенный фильтр автоматически определяет диапазон базы данных, диапазон критериев и диапазон извлеченных значений, если им присвоены имена База_данных, Критерии и Извлечь, соответственно. в) Выставить флажок Только уникальные записи, если необходимо что- бы в результат фильтрации не попали повторяющиеся записи. 4. Нажать кнопку OK. Использование Расширенного фильтра имеет следующие особенности: 1. Диапазон критериев может либо содержать строку заголовка, либо – нет. Если диапазон критериев содержит строку заголовка то: а) Названия полей диапазона критериев должны совпадать с названиями полей в диапазоне базы данных. При несовпадении названий полей в диапазоне критериев и диапазоне базы данных Excel никаких сообще- ний не выдает. Но и искать какие-либо значения в базе данных по несу- ществующему полю не имеет смысла. б) Количество и порядок следования названий полей в заголовке диапазона критериев не обязательно должен совпадать с их количеством и поряд- ком следования в заголовке диапазона базы данных. Незаполненные названия полей в заголовке диапазона критериев не допускаются. 2. Пустому значению поля в диапазоне критериев соответствует любое значе- ние одноименного поля в базе данных. 3. При построении критериев отбора записей в диапазоне критериев поля в нем объединяются по правилу логической операции «И», а записи – «ИЛИ». Например, на следующем рисунке представлен фрагмент таблицы, где в базе данных была найдена одна запись и помещена в диапазон извлеченных значений, у которой Фамилия начинается на букву «и» и(!) Год рождения больше 1965. На втором следующем за ним рисунке показан результат фильтрации той же базы данных на месте для записей, у которых Фамилия начинается на букву «и» и(!) Год рождения больше 1965 или(!) Фамилия начинается на букву «с» и(!) Год рождения меньше или равен 1970. А на третьем рисунке результат того, как будет отфильтрована база дан- ных, если необходимо, чтобы Фамилия начиналась на букву «и» и (!) Год ро- ждения был больше 1965 и (!) меньше или равен 1970. 149 Для снятия фильтра с диапазона базы данных и, соответственно, отображения всех ее записей необходимо выполнить команду Данные → Фильтр → Отобразить все. 4. При повторной фильтрации уже отфильтрованной на месте базы данных будут учитываться все ее записи, в то же время при других операциях, таких, например, как копирование или построение диаграмм – только отображаемые. 5. Диапазон извлеченных значений может либо содержать строку заголовка, либо – нет. Если диапазон извлеченных значений строки заголовка не имеет – она будет добавлена автоматически из диапазона базы данных. Если же он имеет строку заголовка – то в диапазон извлеченных значений будут запол- нены только поля, которые имеют заголовки, даже если они повторяются. 6. При фильтрации базы данных в диапазон извлеченных значений его преж- нее содержание замещается новым. 7. В случае переполнения ограниченного диапазона извлеченных значений выдается соответствующее окно сообщения, в котором можно щелкнуть по кнопке Да – чтобы продолжить копирование и, возможно, уничтожить на- ходящиеся «ниже» данные, или Нет – чтобы прекратить процесс заполне- ния диапазона извлеченных значений. 150 ЛЕКЦИЯ № 7. ОБОБЩЕНИЕ ДАННЫХ 7.1. Структуризация и итоги Понять смысл информации, хранящейся в больших таблицах данных, иногда чрезвычайно трудно из-за слишком подробного ее представления. Од- ним из способов улучшения восприятия таких таблиц является скрытие части строк или столбцов, а если точнее, то – представление информации в виде ие- рархических структур с различной степенью детализации. Скрывать часть строк или столбцов в Microsoft Excel можно нескольки- ми различными способами. Наиболее простой и очевидный из них заключается в установлении ну- левой высоты (для строк) или нулевой ширины (для столбцов): а) методом перетаскивания их границ в области заголовков, или б) по команде Формат → Строка (Столбец) → Скрыть. Однако такой способ не является удобным и наглядным, поскольку ни- как не связан со смыслом хранящейся в таблицах информации. Для отображения же таблиц с различной степенью детализации, в зависимости от хранящейся в них информации, в Microsoft Excel предусмот- рены специальные средства группировки и структуризации данных, а также подведения итогов, которые можно реализовать тремя различными способами: 1. Вручную. 2. Автоматически. 3. Автоматически, с одновременным подведением итогов. Создание структуры вручную. Создание структуры вручную является самым простым (но не самым ра- циональным) методом структуризации и группировки данных. Его рассмотре- ние проведем на примере представленной рис. 7.1 таблицы, в которой необхо- димо скрыть все данные, относящиеся к поставщику Поставщик1. Рис. 7.1 – Исходная таблица 151 Для создания структуры строк на рабочем листе Microsoft Excel вруч- ную необходимо: 1. Выделить подлежащую скрытию строку, например, щелкнув по номеру ее заголовка слева. В нашем примере это любая строка, поле Поставщик кото- рой содержит значение Поставщик1. 2. Выполнить команду Данные → Группа и структура → Группировать. Если до выполнения команды была выделена не целая строка, а лишь одна, или несколько, ее ячеек, то появится окно диалога Группирование, в кото- ром при помощи переключателя Сгруппировать необходимо уточнить объект группировки строки, или столбцы. 3. Повторить пункты 1 и 2 для остальных строк, подлежащих скрытию. После выделения очередной строки для повторения команды Данные → Группа и структура → Группировать достаточно просто нажимать клавишу F4. В результате выполненных действий исходная таблица примет вид, представ- ленный на рис. 7.2. Рис. 7.2 – Таблица с простой 2-х уровневой структурой строк 152 При этом в ней появилось два новых вида элементов управления: 1. кнопки с цифрами, расположенные в левом верхнем углу окна доку- мента, и 2. кнопки со знаками «-», расположенные слева от номеров строк. Кнопки с цифрами служат для отображения данных с уровнем дета- лизации равным номеру на кнопке. Например, для отображения только первого уровня детализации необходимо нажать на кнопку с цифрой 1. При этом уров- ни детализации ниже первого будут скрыты – строки, содержащие в поле По- ставщик значение Поставщик1, отображаться не будут, а знак «-» на всех кнопках слева от номеров строк будет заменен на «+». Для повторного же ото- бражения скрытых строк необходимо нажать на кнопку с цифрой 2. В резуль- тате будут отображены не только скрытые ранее строки, но и знак «+» на кнопках сменится на знак «-». Кнопки со знаками «-» и «+» слева от номеров строк служат для скрытия или отображения, соответственно, не всех значений некоторого уровня дета- лизации, а лишь отдельных из них. Так, например, нажатие кнопки со знаком «-» в строке 14 вызовет скрытие лишь одной строки 13 и превращение знака «- » в знак «+». При этом знак «+» возле строки свидетельствует о том, что здесь скрыты некоторые данные. Щелчок по кнопке со знаком «+» возле строки с номером 14 вызовет отображение строки 13 и, соответственно, превращение знака «+» в знак «-». Создание структур вручную имеет один, весьма существенный, недоста- ток – выделение отдельных строк таблицы, особенно при значительных ее раз- мерах, превращается в довольно-таки утомительную процедуру, не гаранти- рующую однако от появления ошибок. Она также многократно усложняется при необходимости группировки информации по нескольким уровням детали- зации. Естественным способом упрощения этой процедуры является предва- рительная сортировка исходной таблицы по соответствующим полям (в нашем примере – по полю Поставщик). Наиболее широкое применение такой способ формирования структур нашел при создании прайс-листов, и подобных им документов. При этом: 1. Под строкой заголовка выделяется не одна, а несколько строк (составляю- щих, например, некоторую группу товаров). 2. Как обычно, по команде Данные → Группа и структура → Группиро- вать создается очередной уровень группировки. 3. Для того чтобы кнопки сворачивания/разворачивания элементов структур («-»/«+») отображались не ниже от них, а возле соответствующих заголов- 153 ков, необходимо выполнить команду Данные → Группа и структура → Настройка, и в раскрывшемся затем окне диалога Настройка сбросить флажок итоги в строках под данными. Для исключения одной или нескольких строк из группировки необходимо: 1. Выделить их. 2. Выполнить команду Данные → Группа и структура → Разгруппировать. Группировка и разгруппировка столбцов выполняется аналогичным образом. Автоматическое создание структуры. Автоматическое создание структурывыполняется по команде Данные → Группа и структура → Создание структуры. Например, в результате применения этой команды к исходной таблице (рис. 7.1) она будет выглядеть следующим образом. В ней Microsoft Excel построил 2-х уровневую иерархию столбиков. Посколь- ку в столбике F подведены итоги за 1-й квартал, то столбцы C, D и E были объединены в одну группу, так как в них записаны поставки товаров за Ян- варь, Февраль и Март, соответственно. Автоматическое построение структуры таблицы выполняется на основании следующих правил: 1. Структура таблицы определяется исходя из наличия в ней формул, которые определяют зависимости между ячейками. 2. Все ссылки в формулах, как по горизонтали, так и по вертикали, должны идти в одном направлении. Расположение итоговых данных (или заголов- ков), как по строчкам, так и по столбцам, устанавливается при помощи со- 154 ответствующих флажков в окне диалога Настройка, которое активизиру- ется по команде Данные → Группа и структура → Настройка. 3. Microsoft Excel поддерживает до 8-ми уровней структуры, причем они могут иметь как горизонтальную (для строк), так и вертикальную (для столбцов) ориентацию. 4. На каждом рабочем листе может быть построена только одна структура. Автоматическое создание структуры с одновременным подведением итогов Автоматическое создание структуры с одновременным подведением итогов является самым простым способом структуризации таблиц. Оно вы- полняется в соответствии со следующим алгоритмом: 1. Указать таблицу любым известным способом. 2. Отсортировать таблицу по тем полям, по которым необходимо подвести итоги и построить структуру. Для нашего примера – это сортировка по полю Поставщик, а внутри этой сортировки – по полю Товар. 3. Выполнить команду Данные → Итоги. 4. В раскрывшемся в результате окне диалога Промежуточные итоги при помощи стандартных элементов управления выставить параметры подве- дения итогов и построения структуры следующим образом: а) Cписки устанавливают: • При каждом изменении в – поле, при изменении значения которого необходимо подводить промежуточные итоги. • Операция – одну из следующих операций, которая будет выполнять- ся при изменении значения поля, указанного в предыдущем раскры- вающемся списке При каждом изменении в: - Сумма, - Количество, - Среднее, - Максимум, 155 - Минимум, - Произведение, - Количество чисел, - Смещенное отклонение, - Несмещенное отклонение, - Смещенная дисперсия, - Несмещенная дисперсия. Большинство из элементов этого списка имеют очевидные и общепринятые значения, по остальным же – получить исчерпы- вающую информацию в разделе «Итоговые функции для анализа дан- ных» справочной системы MExcel не составляет особого труда. • Добавить итоги по – установить флажки против названий тех полей, по которым необходимо выполнить операцию, указанную в преды- дущем раскрывающемся списке Операция. б) Флажки указывают: • Заменить текущие итоги – на то, что при построении промежуточ- ных итогов текущие итоги должны заменить предыдущие. Его необ- ходимо выставить, если требуется заменить предыдущие итоги, а сбросить – если текущие итоги должны отображаться в таблице вме- сте с предыдущими. • Конец страницы между группами – на необходимость вставлять после каждого промежуточного итога разрыв страницы. • Итоги под данными – на необходимость вывода промежуточных и общих итогов после соответствующих данных. Иначе они будут вы- ведены перед ними. в) Кнопки выполняют: • OK – завершение ввода параметров, поведение итогов и построение структуры. 156 • Отмена – закрыть окно без подведения итогов и построения структуры. • Убрать все – удалить из таблицы промежуточные итоги и ее структуризацию В результате для примера исходной таблицы (рис. 7.1) будут подведены следующие итоги и построена такая структура: Если же для этой таблицы подвести итоги еще по одному полю – Товар (сбросив при этом флажок Заменить текущие итоги в окне диалога Проме- жуточные итоги), то она будет выглядеть следующим образом: При автоматическом подведении итогов и построении многоуровневых структур данных в таблицах необходимо придерживаться следующих правил: 1. Таблица должна быть предварительно отсортирована в соответствии с предполагаемыми уровнями иерархии. В противном случае промежуточ- 157 ные итоги, и соответствующие группировки, будут построены после каж- дого изменения значения в указанном поле, что приведет не к упрощению а, наоборот, к усложнению восприятия таблицы. 2. Для построения многоуровневой структуры данных в таблице такое по- строение необходимо начинать с самого верхнего уровня. При этом флажок Заменить текущие итоги должен быть сброшен при построении всех уровней иерархии, кроме первого. 3. При автоматической группировке смежных строк, относящихся к разным структурным группам, Excel объединяет их в одну группу. Поэтому, для того чтобы при группировке такие строки не были объединены в одну группу, между ними необходимо вставлять, по крайней мере, одну пустую строку. И добавление строк с итогами, в этом смысле, является хотя и вы- нужденным, в какой-то мере, но довольно-таки рациональным решением. После подведения итогов и построения структуры их можно как удалять (вместе или по отдельности), так и добавлять новые направления структуриза- ции. Так, по команде |