2014-02-24
870
Сводные таблицы
Списки
Список — это упорядоченный набор данных, состоящий из строки заголовков (описания данных) и строк данных, которые могут быть числовыми и текстовыми.
Размер списка ограничен размерами одного рабочего листа, т.е. список может иметь не более 256 полей и не более 65 535 записей. Полями принято называть столбцы списка, а записями – строки.
Excel будет считать таблицу списком, если ее формат удовлетворяет следующим условиям.
• Список обязательно должен содержать строку заголовков.
• В каждом столбце должна содержаться однотипная информация. Например, не следует смешивать в одном столбце даты и обычный текст.
• В списке не должно быть пустых строк.
• Рекомендуется помещать список на отдельный лист. Но если все же на лист нужно поместить еще и другую информацию, следите, чтобы список от нее отделялся хотя бы одной пустой строкой и одним пустым столбцом. В противном случае вы рискуете приобрести, например, сотрудника с фамилией «Итого».
Для того чтобы было удобнее работать с большими таблицами, воспользуйтесь командой Окно – Разделить. После того как на экране появятся разделительные линии, буксируйте их мышью таким образом, чтобы горизонтальная линия оказалась точно под строкой заголовков (от вертикальной линии можно отказаться, оттащив ее за пределы рабочего окна). Команда Окно – Закрепить области зафиксирует деление, и заголовки будут видны при прокручивании списка.
Основные приемы работы со списком
Excel обладает мощными средствами для работы со списками. Это:
• пополнение списка с помощью формы;
• фильтрация списка;
• сортировка списка;
• подведение промежуточных итогов;
• создание итоговой сводной таблицы на основе данных списка. Для того чтобы воспользоваться любым из этих инструментов, Нужно установить курсор на одну из ячеек списка.
Ввод данных
При вводе данные можно добавлять непосредственно в ячейки списка, а можно воспользоваться специальной формой ввода.
Если вы выбрали первый способ, не забывайте пользоваться командой контекстного меню Выбратьиз списка, Excel избавит от необходимости много раз набирать один и тот же текст.
Если вы решили прибегнуть к помощи формы ввода, поместите курсор в любое место списка и выберите команду Данные – Форма. На экране появится диалоговое окно, в котором будет отображено каждое поле списка. При этом поля, содержащие формулы, хотя и отображаются в форме ввода, их значения изменить нельзя.
Индикатор в правом верхнем углу формы показывает номер выбранной записи и общее число записей в форме.
Чтобы ввести новую запись, щелкните по кнопке Добавить. Форма очистится, и вы сможете ввести нужную информацию в соответствующие поля. После этого снова щелкните по кнопке Добавить, а если не хотите больше добавлять записи – по кнопке Закрыть.
Вновь введенные данные появятся в конце списка. Формулы, содержавшиеся в ячейках списка, автоматически будут распространены и на новую запись.
Форму ввода можно использовать не только для ввода данных. Она позволяет просматривать существующие записи, редактирован, их, удалять и выборочно отображать данные по определенному критерию.
Фильтрация списков
В Excel существует два типа фильтров: Автофильтр и Расширенный фильтр.
Перед тем как использовать Автофильтр, выделите любую ячейку списка. Затем выберите команду Данные – Фильтр – Автофильтр. При включении Автофильтра возле имен полей списка появляются кнопки со стрелками.
При щелчке по любой из этих кнопок раскрывается меню, содержащее команды и список значений данного поля. С помощью этого меню можно отобрать все записи с заданным значением поля.
Рис. 9.9. Пример использования Автофильтра
Обратите внимание на цвет стрелок на кнопках Автофильтра: если Автофильтр включен, кнопки окрашиваются в синий цвет.
Чтобы отключить ранее заданный фильтр, в раскрывающемся меню кнопок Автофильтра следует выбрать команду Все.
Если задан сложный критерий, то придется отменять составляющие условия отбора по очереди. Иногда бывает проще отказаться от Автофильтра, выбрав команду Данные – Фильтр – Автофильтр, а потом установить Автофильтр снова, или Данные – Фильтр – Отображать все...
Кроме команды Все, в раскрывающемся меню кнопок Автофильтра есть еще одна полезная команда Первые 10..., которая используется для полей числового типа или дат. Эта команда покажет «горячую десятку» вашего списка.
Пусть необходимо узнать расходы за последние три дня. Щелкните по кнопке Автофильтра в столбце Дата, выберите в раскрывшемся меню команду Первые 10..., в диалоговом окне сделайте установки как на следующем рисунке.
В окне «Наложение условия по списку» можно установить любое количество наибольших (или наименьших) элементов, которое вы хотите отобразить. Если вы хотите оставить процент записей (например, 10% наименьших значений), в третьем окне вместо «элементов списка» установите «% от количества элементов». При создании сложного условия отбора команда Первые 10... всегда применяется ко всему списку.
Расширенный фильтр
Часто для отбора нужной информации из списка бывает вполне достаточно Автофильтра или пользовательского фильтра. Однако для решения сложной задачи приходится прибегать к помощи расширенной фильтрации. Расширенный фильтр гораздо гибче Автофильтра, но для того чтобы иметь возможность использовать его, приходится выполнять подготовительные действия.
С помощью расширенного фильтра можно:
• Определить более сложный критерий фильтрации.
• Помещать результат отбора данных на другое место и даже на новый лист рабочей книги.
• Устанавливать вычисляемый критерий отбора.
Рис. 9.10. Расширенный фильтр
Чтобы воспользоваться расширенным фильтром, необходимо задать диапазон критериев.
Диапазон критериев – область рабочего листа, в которой формируется условие (условия) отбора. Диапазон критериев должен состоять, по крайней мере, из двух строк, первая из которых содержит все или некоторые названия полей списка.
Удобнее всего отвести для диапазона критериев область над списком. Названия полей, не используемых при фильтрации, можно не помещать в диапазон критериев. Но если предполагается, что в дальнейшем в зависимости от обстоятельств может понадобиться и другая информация из списка, скопируйте строку, содержащую названия полей списка, целиком.
Условия отбора следует вносить в пустые ячейки диапазона критериев. Условия, расположенные в ячейках одной строки, соединиются оператором И. Условия, расположенные на разных строках, соединяются оператором ИЛИ. Диапазон критериев может состоять из любого количества строк.
Область ячеек, содержащих критерии, должна отделяться oт списка, по крайней мере, одной пустой строкой.
В диалоговом окне расширенного формата установите курсор и поле Диапазон условий, а затем мышью выделите ячейки, содержащие условие отбора. Щелкните по кнопке ОК.
Для того чтобы отключить расширенный фильтр, воспользуйтесь командой Данные – Фильтр – Отобразить все.
При использовании вычисляемого критерия отбор производится «по несуществующему полю». При создании формул вычисляемых критериев всегда ссылайтесь на первую строку списка, а не на строку заголовков. Если в формулу будут подставляться значения вне списка, используйте абсолютные ссылки.
Если отфильтрованный список должен быть помещен на другой лист рабочей книги, сначала перейдите на этот лист и только потом обращайтесь к команде Данные – Фильтр – Расширенный фильтр.
Сортировка
Иногда удобно, чтобы строки в списке имели определенную последовательность. В зависимости от целей может потребоваться чтобы строки располагались в алфавитном порядке по видам продукции или по типам затрат. Или, например, будет необходимо изменить порядок строк таким образом, чтобы суммы затрат располагались в последовательности от большей к меньшей.
Изменение порядка строк в списке называется сортировкой.
Чтобы отсортировать список:
• поместите курсор в тот столбец таблицы, по которому нужно выполнить сортировку;
• затем щелкните по кнопке Сортировка по возрастанию или Сортировка по убыванию.
Однако бывает необходимо в одном столбце упорядочить данные, а в других – оставить все по-прежнему. Например, в результате предыдущей сортировки нарушилась порядковая нумерация. В этом случае следует выделить только нужный столбец таблицы, а затем выполнить сортировку по убыванию.
Сортировка отфильтрованного списка выполняется только для видимых строк.
Если необходимо выполнить сортировку по двум или нескольким столбцам:
• выберите команду Данные – Сортировка;
• в появившемся окне «Сортировка диапазона» в поле Сортировать по выберите название столбца и укажите порядок сортировки;
• затем сделайте то же самое для второго и третьего поля;
• нажмите ОК.
Упорядочивание текстовых строк чаще всего выполняют по алфавиту. Но это не всегда удобно. Для того чтобы установить особый порядок сортировки, например по дням недели или месяцам, выберите команду Данные – Сортировка. Затем нажмите кнопку Параметры. В открывшемся диалоговом окне Параметры сортировки из раскрывающегося списка «Сортировка по первому ключу» выберите полные названия месяцев. Теперь при сортировке по полю «Год» и (или) «Месяц» названия месяцев будут располагаться в привычном порядке.
Создание промежуточных итогов
Итоги, рассчитанные для групп данных, называются в Excel промежуточными итогами.
Для получения промежуточных итогов выполните ряд действий:
• Отсортируйте список. Сортировку следует проводить по тому нолю списка, при изменении значений которого должны рассчитываться промежуточные итоги. При необходимости выполните фильтрацию.
• Выберите команду Данные – Итоги.
• В диалоговом окне «Промежуточные итоги» установите критерии, функцию и другие параметры, которые будут использованы при расчете итогов.
Рис. 9.11. Промежуточные итоги
В раскрывающемся списке При каждом изменении в... показываются названия всех столбцов таблицы. Выбирайте заголовок тою поля, по которому проводили сортировку.
В раскрывающемся списке Операция на выбор даются 11 функций, которые могут быть использованы для расчета итогов. Чате всего используются Сумма и Кол-во значений.
В окне Добавить итоги по следует «галочкой» отметить названия полей списка, для которых надо подвести итоги.
При каждом повторном использовании команды Итоги можно выбирать новую функцию. Если вы хотите, чтобы промежуточные итоги накапливались, то есть каждый последующий уровень итогов не уничтожал предыдущий, опция Заменить текущие итоги должны быть отключена.
Для того чтобы удалить все рассчитанные промежуточные итоги, выберите команду Данные – Итоги и нажмите кнопку Убрать все.
Сводная таблица – динамическая таблица итоговых данных, извлеченных или рассчитанных на основе информации, содержащейся в списках.
Информации в списке много, но она не систематизирована, и ли того чтобы она стала действительно полезной, ее нужно обобщить. Руководителя фирмы могут интересовать, например, такие вопросы:
• Насколько успешно каждый филиал продавал тот или иной тип товара?
• Как распределена сумма, заработанная филиалом, между менеджерами?
• Как продавались товары со скидкой?
Знание механизма сводных таблиц позволит получить ответы на эти вопросы за секунды, при этом не потребуется вводить ни одной формулы.
Создание сводной таблицы
Установите табличный курсор в одну из ячеек списка. Выполните команду Данные – Сводная таблица. На экране появится первое из трех диалоговых окон Мастера сводных таблиц и диаграмм.
Рис. 9.12. Мастер сводных таблиц
На этом этапе определяется источник данных. Так как наши данные хранятся в базе данных рабочего листа, выберите В списке или базе данных Microsoft Excel и переходите к следующему шагу, щелкнув по кнопке Далее.
На втором шаге Мастер попросит уточнить диапазон, в котором хранятся исходные данные. Если при выполнении команды Данные – Сводная таблица курсор был помещен в один из элементов списка, Excel выполнит это автоматически.
Если списку заранее было присвоено имя База_данных, Excel отыщет нужный диапазон, даже если курсор находился в произвольной части рабочего листа. Но, конечно же, можно указать тот диапазон исходных данных, какой пожелаете. Чтобы перейти к третьему этапу, щелкните по кнопке Далее.
В последнем диалоговом окне мастера щелкните по кнопке Макет.
В открывшемся окне поля списка представлены в виде кнопок, тo нужно просто перетащить кнопки в соответствующие области макета сводной таблицы.
Рис. 9.13. Макет сводной таблицы
Страница – значения поля, помещенного в эту область, используются в качестве заголовков страниц сводной таблицы.
Строка – значения поля, помещенного в эту область, используются в качестве заголовков строк сводной таблицы.
Данные – поле (поля), для которого подводится итог в сводной таблице.
Столбец – значения поля, помещенного в эту область, используются в качестве заголовков столбцов сводной таблицы.
В каждую область можно перетащить любое количество полей, но все поля использовать не обязательно. Если случайно перетащили кнопку не в ту область, просто выведите ее за пределы макета.
По умолчанию при перетаскивании кнопки в область Данные будет применена функция Сумм, если поле содержит числовые значения, и функция Счет – в случае нечисловых значений. Если дважды щелкнуть по полю в области данных, функцию можно будет изменить. Обратите внимание, что функция Счет названа словом Количество.
Чтобы вернуться к диалоговому окну Мастера сводных таблиц и диаграмм, нажмите ОК. Осталось только указать местоположение создаваемой сводной таблицы. Выберите переключатель Новый лист.
Рис. 9.14. Размещение сводной таблицы
Тогда Excel вставит новый лист в рабочую книгу и поместит на него вновь созданную сводную таблицу.
Работа со сводными таблицами
Созданную сводную таблицу можно изменять и настраивать, пока она не будет выглядеть так, как нужно. Очень полезной будет при этом панель инструментов Сводные таблицы.
Если панель не появилась автоматически, выполните команду Вид – Панели инструментов – Сводные таблицы.
Рис. 9.15. Панель инструментов Сводные таблицы
Изменение структуры сводной таблицы. Сводная таблица, отображаемая на рабочем листе, имеет кнопки полей. Любую из них можно перетащить мышью в другое место сводной таблицы. Например, можно перетащить кнопку поля из области столбца в область строки. Сводная таблица немедленно изменится, реагируя на внесенные изменения.
Перетаскивая кнопки, можно также изменить порядок полей в областях строк и столбцов.
Удаление поля. Чтобы удалить поле из сводной таблицы, перетащите его за пределы сводной таблицы. После того как вы отпустите кнопку мыши, выбранное поле будет исключено из таблицы.
Добавление нового поля. В тот момент, когда табличный курсор попадает в сводную таблицу, на экране появляется дополнительное окно Список полей сводной таблицы.
В этом окне «жирным» шрифтом выделены названия тех по лей списка, которые нашли свое отражение в сводной таблице.
Если необходимо добавить новое поле в сводную таблицу, выберите кнопку этого поля и окна Список полей сводной таблицы, укажите в раскрывающемся списке нужную область и нажмите кнопку Поместитьв (или просто перетащите кнопку поля в соответствующую область сводной таблицы). Название кнопки поля в сводной таблице можно изменить, просто отредактировав текст в ячейке, содержащей эту кнопку.
По умолчанию при перетаскивании кнопки в область Данные применяется функция Сумма, если поле содержит числовые значения, и функция Счет – в случае нечисловых значений. Дважды щелкните мышью по кнопке Функции. В открывшемся диалоговом окне можно выбрать нужную расчетную функцию.
Рис. 9.16. Список полей сводной таблицы
Параметры полей сводной таблицы. Для полей сводной таблицы предусмотрено несколько опций. Чтобы получить к ним доступ, дважды щелкните мышью по кнопке поля. На экране появится диалоговое окно Вычисление поля сводной таблицы.
Опции диалогового окна:
Имя. Изменяет название, отображаемое на кнопке поля. Также это название можно изменить, просто отредактировав текст в ячейке, содержащей кнопку поля.
Итоги. Изменяет тип промежуточных итогов. Подводить итоги имеет смысл только в том случае, когда в области строк или столбцов сводной таблицы находится несколько полей.
Отображать пустые элементы. Этот флажок нужно установить, чтобы в сводной таблице отображались элементы полей, не содержащие данных.
Форматирование сводной таблицы. При создании сводной таблицы используется автоматическое форматирование. Но когда вводная таблица создана, можно применить другой тип форматирования. Для этого поместите курсор в одну из ячеек сводной таблицы и щелкните на кнопке Формат отчета панели инструментов Сводные таблицы. После того как появится диалоговое окно Автоформат, выберите нужный Автоформат и щелкните по кнопке ОК.
Просмотр деталей. Каждая ячейка в области данных сводной таблицы представляет несколько записей исходной базы данных. Если нужно выяснить, из каких полей состоит суммарное значение, дважды щелкните мышью на нужной ячейке в области данных вводной таблицы. В результате будет создан новый лист с теми записями, которые были использованы для получения сводной информации в выбранной ячейке. Например, щелкните по ячейке В5. В результате на новом листе сформируется следующая таблица.
| Дата | Товар | К-во | Цена | Скидка | Сумма | НДС | Счет | Менеджер | Филиал |
| 16.01.2009 | Товар 1 | 16г | 0,05 | 39662,5 | 7139,93 | Оплачен | Петров | Филиал 2 | |
| 19.01.2009 | Товар 1 | 675,00 | Оплачен | Петров | Филиал 2 |
Создание диаграмм по сводной таблице. Хотя сводную диаграмму можно создавать одновременно со сводной таблицей с помощью Мастера, удобнее это сделать в конце работы, когда сводная таблица уже приняла окончательные очертания. Для этого щелкните на кнопке Мастер диаграмм панели инструментов Сводные таблицы, и сводная диаграмма будет создана на отдельном листе.
По умолчанию это столбиковая диаграмма с накоплением. В отличие от уже знакомых диаграмм, сводная диаграмма имеет такие же кнопки полей, как и сводная таблица.
Если необходимо убрать с диаграммы лишний товар, откройте список кнопки Номенклатура. Если хотите добавить еще одного сотрудника, воспользуйтесь кнопкой Сотрудник. Если вместо результирующей функции Сумма необходимо найти Среднее или Максимум, дважды щелкните по кнопке Сумма по полю Сумма.
В верхней части диаграммы расположена область Перетащите сюда поля страниц.
В сущности, эта сводная таблица не совсем корректна, так как показывает результирующие значения по оплаченным и по неоплаченным счетам. Исправим эту ошибку. Пусть полем страницы будет Счет. Перетащим мышью это поле из окна Список полей сводной таблицы. Тогда в левом верхнем углу диаграммы поя вшей список.
Раскрыв список страниц, можно выбрать, какую именно информацию следует отобразить на диаграмме: всю, об оплаченных счетах или о неоплаченных счетах.
Обновление сводных таблиц. Обратите внимание на то, что сводные таблицы не обновляются автоматически: Excel должен заново пересчитывать всю сводную таблицу, когда изменяют исходные данные.
