Глава 6 (О.Б. Калугина, В.С. Люцарев - Работа с электронными таблицами. Microsoft Office Excel 2003)

Списки в Excel139Глава 6Списки в ExcelТаблицы на рабочих листах Excel, аналогичные базам данных, принятоназывать списками. В чем заключается аналогия? Прежде всего, в требовании однородности данных в ячейках каждого из столбцов таблицы. В первойстроке такой таблицы обычно указываются названия столбцов (или, в терминологии, принятой в работе с базами данных, полей). В последующихстроках (записях) – конкретные значения по каждому полю.Команды за словом Данные в строке меню окна Microsoft Excelпредназначены для ведения и обработки списков – их сортировки (упорядочение записей), фильтрации (отбору записей по заданным критериям), созданию различных сводок и т.д.Списки подчас бывают очень длинны, занимая не одну сотню, аиногда и тысячу строк.

Корректное выделение мышью полного такогосписка затруднительно. В Microsoft Excel предусмотрены возможности,облегчающие эту задачу.140Глава 6Выделение списка на рабочем листеДля выделения списка удобно использовать клавиши со стрелками1в сочетании с удерживаемыми в нажатом состоянии клавишами Ctrl иShift – будет выделен диапазон ячеек: от активной ячейки2 до последнейзаполненной на листе в указанном стрелкой направлении.

СочетаниеShift + клавиша со стрелкой увеличит (или уменьшит) выделенную область на одну строку или столбец в зависимости от направления стрелки.Последовательные нажатия клавиши F8 включают/отключают специальный режим для выделения ячеек на рабочем листе. При включенном режиме – в строке состояния окна присутствует индикатор ВДЛ –клавиши перемещения курсора позволяют изменить выделенный диапазон ячеек.Если диапазону списка не присвоеноимени3, то для Excel он ограничивается на рабочем листе «пустыми» строкой и столбцом.На соблюдении этого требования основано«распознавание» списка на рабочем листе командами, собранными за словом Данные. Достаточно активизировать любую ячейку внутрисписка и подать нужную команду (например,Данные Сортировка…) – автоматическибудет выделен весь диапазон ячеек, ограниченный первыми встретившимися пустыми строками и столбцами.123Клавиши перемещения курсора.или предварительно выделенного блока ячеек.О присвоении имен ячейкам и диапазонам ячеек рассказано в параграфе Имена ячеек идиапазонов в главе 1.Списки в Excel141142Глава 6Сортировка данных143Списки в ExcelЕсли перед щелчком по кнопке сортировки в таблице был выделендиапазон ячеек, на экране появится предупреждающее диалоговое окно.В панели инструментов Стандартная обычно присутствуют кнопкидля сортировки данных по возрастаниюили убыванию.

Щелчокпо одной из них произведет сортировку в указанном кнопкой порядкепо тому столбцу, внутри которого находилась активная ячейка. Список,подлежащий сортировке, «распознается» при этом автоматически.Нетрудно догадаться, что выбор опции Сортировать в пределах указанного выделения в случаях, аналогичных примеру на рисунке, можетпринципиально нарушить содержимое списка.Более сложную сортировку позволит произвести команда Данные Сортировка… Перед ее подачей достаточно активизировать любуюячейку внутри списка – диапазон будет выделен автоматически, а на экране появится диалоговое окно.Раскрывающийсясписок содержитназвания столбцовсортируемогодиапазонаПервая строкане включенав сортировкуЕсли первая строка списка содержит названия столбцов, ее ячейкиудобнее «различать» не только содержимым, но и форматом – в данныедля сортировки первая строка по умолчанию включена не будет, работасо списком зрительно облегчается.Первая строкавключается всортировкудиапазона144Глава 6Применение автофильтраДля выборки данных из списка по заданному критерию предусмотрены фильтры.

Убедившись, что активна любая ячейка внутри списка,нужно подать команду Данные Автофильтр – к каждой ячейке первой строки списка будет добавлена стрелка раскрывающегося меню.145Списки в ExcelПо каждому из столбцов списка можно задать различные условиядля отбора записей. Строка Условие… из раскрывающегося меню автофильтра предложит диалоговое окно, в котором указывают необходимые ограничения.Значениедля сравненияОперациясравненияРаскрывающийся список предлагает выбрать операции сравнения.Первые пять строк меню будут одинаковы для автофильтра по любому из столбцов списка – сортировка списка по указанному столбцу ввозрастающем или убывающем порядке, (Все), (Первые 10…), (Условие…). Последующие строки меню автофильтра перечисляют в алфавитном порядке уникальные значения конкретного столбца списка.Выбором (Все) отменяется фильтр в указанном столбце, если онбыл ранее применен.Фильтр (Первые 10…) применим в списке только к столбцамчисловых значений.

На экране появится диалоговое окно, в которомможно задать количество наибольших (или наименьших) по значениюэлементов списка.О произведенном отборе свидетельствует голубой цвет номеров строксписка1 и стрелки автофильтра столбца, по которому был выполнен отбор.1Наличие на рабочем листе скрытых строк.Для числовых значенийДля текстовых значенийЗначения для сравнения также можно выбрать из раскрывающегося списка1 или ввести с клавиатуры.

При задании критерия отбора текстовых значений можно использовать символы ? и *. Например, равноA* эквивалентно условию «начинается с А», равно А??? означает условие отбора всех текстовых значений из 4 символов, начинающихся с «А».Можно задать для проверки сразу два условия. Если они должнывыполняться одновременно, то между условиями ставится логическаяоперация «И», если достаточно выполнения хотя бы одного из двух поставленных условий – операция «ИЛИ».В качестве примера приведем условия для фильтра, отбирающегоиз общего списка только записи со значениями в поле Группа 101, 102или 1102.23В нем перечислены все имеющиеся значения поля, указанного для фильтра.Номера групп варьируются в списке от 101 до 112.146Глава 6Критерии отбора могут быть установлены как по одному, так и понескольким полям списка.

Например, чтобы из уже отфильтрованногопо указанным группам списка студентов выбрать только тех, для которых рейтинг успеваемости не ниже 45, следует установить еще одинфильтр – по полю Рейтинг.Списки в Excel147Понятие отчета сводной таблицыОтчет сводной таблицы представляет собой построенную на рабочем листе интерактивную таблицу, в которой по заданному макету обобщенно представлены данные какого-либо списка или базы данных.Отказаться сразу от всех результатов применения фильтров, вернувна рабочий лист полный список, можно командой Данные Фильтр Отобразить все1.Чтобы отказаться от установленной для списка возможности автофильтра, нужно повторно подать команду Данные Фильтр Автофильтр2.Выполните задание 6.1 по теме Списки в Excel из приложения От теории к практике, чтобы научиться сортировать данные на листе, получать выборки по заданномукритерию.1Визуальная подсказка об отсутствии скрытых в списке данных – строки рабочего листапронумерованы черным цветом.2 Сбросить флажок автофильтра.В процессе построения сводной таблицы Microsoft Excel группируетзаписи списка по полям группировки (какие из полей списка должны бытьполями группировки, определяет пользователь).

В одну группу попадутзаписи списка, имеющие одинаковые значения во всех полях группировки. Так, на рисунке, приведенном выше, два поля группировки: Странапоставщик и Наименование. Внутри каждой группы суммируются значе-148Глава 6ния какого-либо другого поля списка, называемого полем данных (в примере это поле Сумма), а в таблице приводится результат.Наряду со сводной таблицей по текущему ее состоянию можно создать и сводную диаграмму. Этот вопрос мы также обсудим в этой главе.Списки в ExcelТретий шаг мастера предполагает указание местоположения в книгесоздаваемой сводной таблицы, ее макет и параметры.Если таблицу предполагается разместить на существующем листе книги,нужно указать адресячейки, определяющийее верхний левый уголСоздание сводной таблицыПостроение сводных таблиц и сводных диаграмм происходит с помощью мастера, который вызывается командой Данные Своднаятаблица…1 .

На первом шаге Мастера сводных таблиц и диаграмм задаются тип источника данных и форма создаваемого отчета (сводная таблица со сводной диаграммой или только сводная таблица).После щелчка по кнопке Макет… предоставляется возможностьсамостоятельно определить, какие поля исходного списка и как нужноразместить в сводной таблице.Вызов справки попостроению сводныхтаблицЩелчок по кнопке Далее > осуществит переход к следующему шагуработы с мастером. В диалоговом окне второго шага следует либо проверить правильность адреса диапазона с исходными данными, либо задатьэтот диапазон самостоятельно.Если Вы рассчитываете на автоматическое распознавание списка, по данным которогобудет построена сводная таблица, не забудьте сначала активизировать любую ячейку внутри этого списка, а затем подать команду.Все имеющиесяполя диапазонас исходнымиданнымиПеретаскивая кнопки с названиями полей в соответствующие области (Страница, Строка, Столбец, Данные), Вы создаете макет будущейтаблицы, например:В последнем случае можно также воспользоваться кнопкой Обзор…,чтобы открыть другой файл с данными для сводной таблицы и задать диапазон в нем.1149В области Данные незабудьте задать полесводных значений1Ниже Вы увидите, что макет уже построенной на рабочем листе сводной таблицы можетбыть легко видоизменен.ОбластьстраницНазначение этих параметров гораздо проще оценить, взглянув хотябы раз на результат построения отчета сводной таблицы.

Для этого, выбрав на третьем шаге мастера сводных таблиц и диаграмм местоположение сводной таблицы в книге, следует щелкнуть по кнопке Готово.По макету, предложенному на приведенном выше рисунке диалогового окна Мастер сводных таблиц и диаграмм – макет, была полученанекоторая сводная таблица (например, представленная на рисунке следующей страницы). Сопоставьте ее с макетом.Отдельно подчеркнем, что по завершении работы мастера сводныхтаблиц и диаграмм мы получаем «первый вариант» сводной таблицы.Этот вариант может быть легко модифицирован – можно прямо в имеющейся таблице перераспределять поля исходного списка.Панель инструментов дляработы со сводной таблицейОбласть данныхОбластьстрокПо заданному макету расположения строк и столбцов можно создать «многостраничный» отчет сводных таблиц. Для этого в областьстраниц нужно поместить хотя бы одну из кнопок полей.

В результате всводной таблице будет добавлена возможность получить сводку не только по всем, но и по каждому отдельному значению указанного в областистраниц поля.Удалить поле из сводной таблицы можно, выведя его кнопку мышью за пределы макета. Щелчок по кнопке ОК в диалоговом окне Мастер сводных таблиц и диаграмм – Макет подтвердит готовность исходного варианта макета сводной таблицы1.Щелчок по кнопке Параметры… в окне третьего шага мастера сводных таблиц и диаграмм вызовет диалоговое окно для уточнения параметров формата сводной таблицы и источника данных для нее.151Списки в ExcelОбластьстолбцовГлава 6Любое из имеющихся в исходномсписке полей можно добавить вуже готовую сводную таблицу150152Глава 6Списки в ExcelОбласть страницРедактирование сводной таблицыЕсли в области страниц находится хотя бы одно поле, это даст возможность получить отчеты сводных таблиц одного и того же макета как повсем возможным значениям этого поля, так и по каждому в отдельности.Обновление данных153Если в исходном списке с данными, по которым была построенасводная таблица, произошли изменения, для обновления этих данныхследует щелкнуть по кнопкев панели инструментов Сводные таблицы1, тем самым подав команду Обновить данные.Сортировка данныхВыделив нужное поле, можно просто щелкнуть по нужной кнопкесортировкив панели инструментов Стандартная.Но для сортировки сводных таблиц предусмотрена и отдельнаякоманда.