Вопрос 4. Деловая графика в среде Excel

Деловая графика предназначена для наглядного представления числовых данных, отображения соотношения различных значений или динамики изменения показателей.

Она включает диаграммы (графики) различных видов. В Excel диаграмма может быть создана на отдельном листе диаграмм или как графический объект на рабочем листе.

Основными элементами диаграммы являются: область диаграммы, область построения диаграммы, ряды и маркеры данных, оси координат, заголовки, легенда, линии сетки, подписи данных. Диаграмма имеет определенный тип и состоит из серий (рядов) данных и оформительских элементов (заголовка, осей, меток, легенды, произвольного текста).

Серией называется ряд (строка или столбец) точек данных, которые отображаются на диаграмме. Диаграмма по умолчанию строится в предположении, что серии расположены по рядам: текст из первого столбца таблицы данных будет вставлен в легенду в виде имен серий, а текст первого ряда (имена категорий) – в виде меток засечек горизонтальной строки.

Данные строки (или столбца) отображаются в диаграмме в виде ряда данных. Если для ряда данных используются значения строки, то значение столбцов образуют категории. Вертикальная ось (ОY) используется для шкалы значений. Все другие обозначения представлены на диаграмме в так называемой легенде. Легенда служит для идентификации рядов данных (категорий). Каждый ряд данных на диаграмме представлен определенным цветом или узором.

В зависимости от вида графического представления данных диаграммы различаются по типу. Excel предлагает пользователю различные типыдиаграмм:

· Гистограмма (в т.ч. объемная):отдельные значения представлены вертикальнымистолбиками различной высоты.

· Линейчатая диаграмма (в т.ч. объемная):отдельные значения представлены полосами разной длины, расположенными горизонтально вдоль оси ОХ.

· Круговая диаграмма (в т.ч. объемная):сумма всех значений принимается за 100%, а процентное соотношение величины изображается в виде круга, разбитого на несколько секторов разного цвета. Допускается только один ряд данных.

· Кольцевая диаграмма: особая форма круговой диаграммы. Сумма всех значений принимается за 100%, а ряды данных представляют собой вложенные кольца, разделенные на сегменты в процентном соотношении.

· Диаграмма с областями:отдельные ряды данных представлены в виде закрашенных разными цветами областей.

· График (в т.ч. объемный):все отдельные значения будут соединены между собой линиями, полученными путем интерполяции.

· Радар: категории представлены лучами, исходящими из одной точки. Каждое значение ряда данных будет выделено на оси и соединено с другими замкнутой линией.

· XY (Точечная) диаграмма: отдельные значения таблицы представлены в декартовой системе координат.

· Объёмная поверхностная диаграмма:совокупность всех значений отображается на диаграмме в виде некоторой поверхности, области которой представляют собой ряды данных.

Диаграмма, вставленная в документ, динамически связана с таблицей данных, т.е. изменяется в соответствии со сделанными изменениями исходных данных.

Построение диаграммы

Построение диаграмм осуществляется с помощью Мастера диаграмм.

Рассмотрим порядок построения на основе таблицы 5.1. Учет товаров на складах предприятия круговой диаграммы, отображающей остаток каждого товара на складах предприятия на начало месяца. Диаграмму разместим на отдельном листе диаграмм с именем Остаток.

1. Выделите на листе Товары диапазон ячеек С4:С8 с исходными данными (область данных диаграммы).

2. Выполните команду Вставка► Диаграммаили нажмите кнопку [Мастер диаграмм] на панели инструментов.Открывшеесяокно мастера диаграмм осуществит построение новой диаграммы за четыре шага.

3. В окне Мастер диаграмм (шаг 1 из 4): тип диаграммы на вкладке Стандартныев списке Тип выберите круговая, из форматов данного типа в списке Вид выберите вариант, описанный как Круговая диаграмма. Если нажать и удерживать кнопку [Просмотр результата], то можно увидеть образец диаграммы. Нажмите кнопку [Далее].

4. В окне Мастер диаграмм (шаг 2 из 4): источник данных диаграмм на вкладке Диапазон данныхпроверьте правильность установленного диапазона,а для задания ориентации рядов установите переключатель Ряды в столбцах. На вкладке Рядустановите курсор в поле Подписи категорий и, перейдя на лист Товары, выделите ячейки В4:В8 для задания в качестве элементов легенды код материала. Нажмите кнопку [Далее].

5. В окне диалога Мастер диаграмм (шаг 3 из 4): параметры диаграммы на вкладке Заголовки в поле Название диаграммывведите заголовок диаграммы – Остаток товаров на начало месяца. На вкладке Легенда установите флажок Добавить легендуи выберите размещение легенды – внизу. На вкладке Подписи данных в группе переключателей Подписи значений установите опцию – значение. Нажмите кнопку [Далее].

6. В окне диалога Мастер диаграмм (шаг 4 из 4); размещение диаграммыустановите переключатель – отдельном,а в правом поле введите имя листа – Остаток. Нажмите кнопку [Готово]. Полученная диаграмма представлена на рис. 5.9.

Редактирование диаграмм

Созданные диаграммы можно корректировать, вплоть до изменения исходных данных и способа их представления. Редактирование диаграмм предполагает изменение типа диаграммы или ориентации данных, изменение визуальных эффектов. Можно также добавлять или удалять отдельные элементы, либо изменять определенные параметры. В Excel предусмотрена также возможность дополнения диаграмм другими рядами данных.

Диаграмму, созданную на рабочем листе, перед обработкой необходимо выделить двойным щелчком мыши. При выделении диаграммы на рабочем листе или листа диаграммы изменяется строка меню. В ней будут представлены команды, используемые для обработки и форматирования диаграмм.

Рассмотрим порядок изменения типа построенной диаграммы Остаток товаров на начало месяца на тип гистограмма.

1. Перейдите на лист Остаток.

2. Выполните команду Диаграмма►Тип диаграммы. В списке Тип выберите – гистограмма, Вид – Обычная гистограмма. Нажмите [ОК].

3. Выполните Диаграмма►Параметры диаграммы►вкладка Легенда. Снимите флажок Добавить легенду и нажмите [ОК].

Вид полученной диаграммы представлен на рис. 5.10.

Рассмотрим порядок добавления на диаграмму новых рядов данных. Добавим к диаграмме Остаток товаров на начало месяца данные Приход, Расход и Остаток на конец месяца.

1. На листе Остатоквыполните команду Диаграмма►Добавить данные. В диалоговом окне Новые данные установите курсор в поле Диапазон.

2. На листе Товары выделите с помощью мыши диапазон ячеек D4:F8, данными из которых будет дополнена диаграмма. Нажмите [ОК].

3. Выполните команду Диаграмма►Параметры диаграммы►вкладка Легенда. Установите флажок Добавить легенду и размещение – внизу. Нажмите [ОК].

На данной диаграмме (рис. 5.11) основу рядов будут составлять показатели остатков на начало и конец месяца, прихода и расхода по каждому товару. Категориями выступают коды товаров.

4. Измените названия элементов легенды Ряд1, Ряд2 и Ряд3. Для этого выполните команду Диаграмма►Исходные данные►вкладка Ряд. В списке Ряд выделите элемент Ряд1, установите курсор в поле Имя и на листе Товары активизируйте ячейку С3, которая содержит заголовок Остаток товаров на начало месяца.

5. Далее в списке Ряд выделите элемент Ряд2, установите курсор в поле Имя и на листе Товары активизируйте ячейку D3, которая содержит заголовок Приход. Аналогично измените названия элементов Ряд3иРяд4, задав для них в качестве названия, содержимое ячеек Е3 и F3 листа Товары.

6. Нажмите [ОК] в окне Исходные данные.

Рассмотрим порядок изменения заголовка диаграммы и названий осей координат на диаграмме.

1. Выполните команду Диаграмма►Параметры диаграммы►вкладка Заголовки.

2. В поле Название диаграммы введите Учет товаров на складах предприятия.

3. В поле Ось Х (категорий) введите Коды товаров, а в поле Ось Y(значений) – Количество. Нажмите [OK].

Вид полученной диаграммы представлен на рис. 5.11.

Рассмотрим алгоритм удаления на диаграмме ряда данных Остаток товаров на начало месяца.

1. На диаграмме выделите ряд Остаток товаров на начало месяца, щелкнув на одном из его маркеров.

2. Нажмите клавишу [Delete] на клавиатуре.

Форматирование диаграмм

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

Рассмотрим порядок изменения цвета для рядов данных, снятия линий сетки, установки параметров для элементов диаграммы: шрифта – 14 пт, выравнивание подписей значений для ряда Приход – под углом 90 градусов.

1. Активизируйте лист Остаток.

2. Выделите на диаграмме ряд данных Приходщелчком мыши по любому маркеру ряда на диаграмме.

3. Выполните команду Формат►Выделенный ряд►вкладка Вид. В поле Граница измените тип линий и цвет, установите флажок с тенью.

4. В поле Заливка выберите цвет для ряда щелчком мыши или воспользуйтесь кнопкой [Способы заливки] для выбора узора или текстуры для ряда. Нажмите [OK].

5. Самостоятельно измените цвет для остальных рядов.

6. Выделите Область диаграммы одним щелком мыши и выполните команду Формат► Выделенная область диаграммы►вкладка Шрифт.

7. Установите размер шрифта – 14 пт и нажмите [ОК].

8. Выделите подписи данных для ряда Приходщелчком по любой из подписей ряда и выполните Формат► Выделенные подписи данных►вкладка Выравнивание.

9. В поле Ориентация выберите 90 градусов и нажмите [ОК].

10. Выполните команду Диаграмма►Параметры диаграммы►вкладка Линии сетки. Снимите в этом окне все флажки и нажмите [ОК].

Вид полученной диаграммы представлен на рис. 5.12.

Вопрос 5. Защита данных

При работе с таблицей Excel бывает необходимо защитить рабочие книги, отдельные ячейки, диаграммы и другие объекты от несанкционированного доступа или нежелательного изменения. Excel позволяет это сделать с помощью специальных действий. После включения функции защиты нельзя изменить заблокированный элемент. По умолчанию Excel защищает все ячейки (на вкладке Защита диалогового окна Формат ячеек по умолчанию установлен флажок Защищаемая ячейка), графические объекты, диаграммы от изменения.Однако защита не вступает в силу пока не выбрана команда Сервис►Защита►Защитить лист. В результате выполнения этой команды будет открыто диалоговое окно Защитить лист(рис. 5.13).Установленные в нем варианты защиты применяются только к текущему листу.

Обычно от изменения защищают ячейки с формулами, названия и шапку таблицы. Если же пользователь попытается ввести информацию в защищенную ячейку таблицы, то выдается сообщение Ячейка или диаграмма защищена от изменений.

В большинстве случаев нет необходимости в блокировке всех ячеек рабочего листа. Перед защитой листа необходимо выделить те ячейки, которые необходимо оставить незаблокированными, и в окне Формат ячеекна вкладке Защита снять флажок Защищаемая ячейка.Тогда при вводе в таблицу данных для перемещения курсора по пустым незащищенным ячейкам можно использовать клавишу [Тab].

Снимается защита командой Сервис►Защита►Снять защиту листа.

Рассмотрим порядок установки защиты на лист Товары для блокировки в табл. 5.1 Учет движения материалов на складах заголовка, названий столбцов и формул.

1. Активизируйте лист Товары.

2. Для того, чтобы оставить ячейки А4:Е8 доступными для редактирования после защиты листа, выделите их и выполните команду Формат► Ячейки►вкладка Защита.Снимите флажок Защищаемая ячейкаи нажмите [ОК].

Установите защиту на лист, выполнив команду Сервис►Защита► Защитить лист. Нажмите [ОК]. После проверки работы защиты снимите ее командой Сервис►Защита►Снять защиту листа.Вопрос 6. Работа с таблицей как с базой данных

Для управления большими массивами данных используются специальные программы, предназначенные для работы с базами данных. В Excel также имеются средства для обработки данных, организованных по аналогичному принципу, – это, так называемые, функции списка. В виде списка можно представить таблицу, строки которой содержат однородную информацию. Список характеризует не содержимое таблицы, а способ ее организации. Отдельные записи (комплекты данных списка) должны быть однородны по строкам и/или столбцам. В таблице не должно быть объединенных ячеек! Однако только однородность данных списка по строкам делает возможным применение функции фильтров (если данные однородны по столбцам, таблицу следует транспонировать). Примером базы данных является список адресов, в котором указаны названия и адреса различных фирм или фамилии людей.

Excel распознает списки автоматически. Поэтому если надо, например, отсортировать данные в списке по определенному критерию, достаточно выделить любую ячейку списка. После активизации функции сортировки Excel автоматически выделит всю область списка.

При автоматическом определении (и выделении) списка признаком конца области списка считается первая пустая строка. Поэтому предварительно следует удалить из списка пустые строки или выделить вручную область, для которой должна быть применена функция списка. Предварительное выделение области необходимо также в том случае, если функция должна быть применена только к определенной части списка. Функции списка нельзя использовать, если выделены несмежные области.

При автоматическом выделении области списка Excel в поисках меток (названий) столбцов сравнивает содержимое первой и второй строк области списка. Если данные в этих строках различаются по типу, Excel принимает первую (верхнюю) строку в качестве строки названий. Данные этой строки будут исключены из обрабатываемой области списка.

Если Excel не обнаружит различий между типами данных в первой и второй строках, то на экране появится сообщение о том, что программа не смогла обнаружить названий для образования меток столбцов. В этом случае программа выдаст запрос, следует ли использовать в качестве меток столбцов данные первой строки выделенной области. Пользователь может согласиться нажатием[ОК] или отменить выполнение операции.

Основными возможностями при работе с базами данных являются:

· организация ввода данных;

· просмотр данных;

· поиск данных по заданному критерию;

· сортировка данных;

· фильтрация данных;

· подведение итогов.

Для работы с таблицей как с базой данных в Excel используется пункт меню Данные.Эта команда корректно работает только с таблицами, в которых названия колонок занимают одну строку. Так, например, для работы с командой Данные таблица Учет движения материалов на складах должна быть преобразована к виду, представленному на рис. 5.14.

Приведем таблицу Учет движения материалов на складах к виду, представленному на рис. 5.14.

1. Создайте копию листа Товары, чтобы легко было вернуться к первоначальному порядку данных. Для этого активизируйте его и выполните команду Правка►Переместить/скопировать лист. В окне Переместить или скопировать установите флажок Создавать копию и нажмите [ОК]. Переименуйте полученный лист Товары(2) на Сортировка.

2. Для преобразования таблицы сделайте следующее:

· выделите строку 2 щелчком мыши на номере строки и удалите ее командой Правка►Удалить. Аналогично удалите итоговую строку.

· Дополните шапку таблицы недостающими заголовками.

Работа с формой

Удобным средством для работы с таблицей является форма (окно формы открывается по команде Данные►Форма. В окне формы (рис.5.15) отображаются все поля одной записи базы данных: слева располагаются названия полей, рядом с ними их значения, доступные для редактирования. Если поле вычисляемое, то оно не доступно для редактирования.

Работа с формой предполагает:

1. Перемещение по полям записи мышью или клавишей [Tab];

2. Перемещение по записям с помощью полосы прокрутки и кнопок [Назад] и [Далее];

3. Ввод или просмотр записей. Достоинством ввода информации через форму является автоматическое копирование формул и автоматическая поддержка форматов данных.

4. Удаление и добавление записей с помощью кнопок [Добавить] и [Удалить]. При добавлении новой записи, она всегда добавляется в конец таблицы.

5. Редактирование значений полей в записях.

6. Поиск данных по критерию с помощью кнопки [Критерии], после нажатия которой в появившемся окне в соответствующих полях задаются критерии. Задание критериев позволяет просматривать через окно Формы только те записи, которые удовлетворяют некоторым условиям поиска (критериям).

Перед тем как начать поиск записей по критерию, рекомендуется сделать текущей первую запись базы данных. Просмотр отобранных записей по критерию осуществляется с помощью кнопок [Назад] и [Далее]. При задании критериев поиска можно использовать символы подстановки:

* – для обозначения произвольного количества символов;

? – для обозначения одного символа.

Например, при необходимости выбора всех записей с фамилиями студентов, начинающихся с буквы «К», в качестве критерия поиска следует ввести К*. А при задании критерия К?рсанов, будут выбираться фамилии Кирсанов, Керсанов, Корсанов и т.п.

В критериях при поиске числовых значений можно использовать операторы сравнения: =, <, >, <>, <=, >=.

Чтобы задать несколько критериев поиска, следует указать их в различных полях, тогда они объединяются логическим И. Задание нескольких критериев позволяет сузить область поиска.

Сортировка данных

Упорядочение табличных данных в выделенных строках по алфавиту, величине или дате называется сортировкой. Столбец, определяющий порядок данных, называются ключом сортировки. Сортировка производится в убывающем или возрастающем порядке. Для сортировки в Excel используется команда Данные►Сортировка. Можно задавать три уровня сортировки одновременно: за одну сортировку можно выполнить сортировку сначала по первому уровню, потом в полученном списке – по второму, а затем – по третьему (рис. 5.16).

Если сортировка ведется по нескольким ключам, то строки с одинаковыми значениями в столбце, указанном в поле Сортировать по, сортируются в порядке, определяемом столбцом, указанным в поле Затем по. Строки с одинаковыми значениями в первых двух столбцах сортируются по столбцу, указанному в поле В последнюю очередь, по.

Рассмотрим порядок сортировки таблицы Учет движения материалов на складах по двум ключам: 1 – номер склада, 2 – код материала.

1. Активизируйте лист Сортировка.

2. Установите курсор в любую ячейку диапазона A2:F7, который необходимо отсортировать, т.е. диапазона таблицы вместе с шапкой.

3. Выберите команду Данные►Сортировка. В окне Сортировка диапазона (рис. 5.16) в раскрывающемся списке Сортировать по выберите поле, по которому следует выполнить сортировку в первую очередь – это № склада. Поставьте переключатель справа в положение – по возрастанию.

4. В поле Затем по выберите второй ключ сортировки – поле Код материала, способ сортировки – по возрастанию. Нажмите кнопку [ОК]. Вид таблицы после выполнения сортировки представлен на рис. 5.17. Обратите внимание, что записи, имеющие одинаковые значения номера склада, выстроились в порядке возрастания кода материала.

Фильтрация данных

Excel дает возможность выборочно работать с данными, удовлетворяющими условиям (критериям) поиска, которые задает пользователь. Процесс выбора данных называется фильтрацией. Фильтрация происходит в пределах предварительно выделенного диапазона или заданной таблицы.

В Excel существует возможность фильтрации данных с помощью Автофильтра и Расширенного фильтра.

Использование автофильтра

С помощью функции автофильтра выбор отдельных записей можно производить непосредственно в самой таблице. При этом для выбора данных можно задавать целый ряд различных критериев. Активизация функции автофильтра происходит путем выбора команды Данные►Фильтр► Автофильтр.Фильтрация таблицы оставляет на экране для обработки только те записи, которые удовлетворяют критериям, остальные строки становятся скрытыми. Команда вызова Автофильтра помещает кнопки раскрывающихся списков в названия полей, при помощи этих кнопок задаются критерии отбора. Стрелки кнопок раскрывающихся списков тех полей, которые задействованы в критерии, меняют цвет с черного на голубой.

Если задать критерии отбора в нескольких столбцах (полях), то они связываются между собой по принципу логического И.

Автофильтр предоставляет несколько видов фильтрации данных:

1. Выбор записей с заданным значением поля происходит путем выбора в раскрывающемся списке значения поля для поиска точного соответствия.

2. Выбор записей по условию производится командой Условие, в результате чего открывается диалоговое окно Пользовательский автофильтр, где задают критерии с участием одного или двух условий с использованием шаблонов(”?” и “*”) , операций сравнения и логических операций И, ИЛИ.

3. Выбор первых наибольших или наименьших n значений позволяет выполнить команда Первые 10.

4. Команда Все восстанавливает на экране все скрытые фильтром строки таблицы.

Рассмотрим использование автофильтра на примере таблицы Учет движения материалов на складах. Пусть из таблицы необходимо выбрать информацию о материалах со склада №2, у которых остаток на конец месяца больше 50.

1. Создайте копию листа Сортировка и назовите его Автофильтр.

2. Установите курсор в любую ячейку диапазона A2:F7.

3. Выберите команду Данные► Фильтр► Автофильтр.

4. Ячейки с названиями полей превращаются в раскрывающиеся списки.

5. Раскройте список в столбце № склада для включения в критерий.

6. Выберите строку, где номер склада равен 2.

7. Раскройте список столбца Остаток на конец месяца.

8. Выберите команду Условие. В окне Пользовательский автофильтр (рис. 5.18.) в левом верхнем раскрывающемся списке выберите операцию сравнения больше, в правом – введите значение 50и нажмите [ОК]. На экране останется одна строка, удовлетворяющая условиям отбора. Остальные строки таблицы окажутся скрытыми.

9. Для отмены всех условий отбора надо выполнить команду Данные► Фильтр►Отобразить все.

Чтобы отменить режим Автофильтра, необходимо повторно выбрать команду Данные►Фильтр►Автофильтр.

Использование расширенного фильтра

Расширенный фильтр предоставляет широкие возможности поиска и фильтрации. Он позволяет не только применять операции И, ИЛИ, но и составлять вычисляемые критерии. Кроме того, отфильтрованные данные могут быть скопированы в заданный диапазон рабочего листа.

При работе с расширенным фильтром создаются три области:

1. Исходный диапазон. Область, где хранятся исходные данные. Содержит диапазон, подлежащий фильтрации, т.е. исходную таблицу обязательно вместе с шапкой таблицы.

2. Диапазон условий. Область на рабочем листе, где задаются критерии поиска информации. Диапазон условий применяется для задания условия отбора записей и формируется из строки заголовков полей, которые будут ключевыми при отборе записей, и строки или строк критериев.

При создании диапазона условий необходимо пользоваться следующими рекомендациями:

· В верхней строке диапазона критериев должны быть расположены имена, в точности, совпадающие с заголовками столбцов исходного диапазона. Можно перечислить все имена, но это необязательно, достаточно указать только те, которые определяют условия отбора. Точное соответствие имен полей исходного диапазона и имен, указанных в диапазоне условий, проще всего обеспечить копированием из самой таблицы.

· Ниже должна располагаться хотя бы одна строка, где задаются условия. В условия отбора расширенного фильтра может входить несколько условий, накладываемых на один столбец, несколько условий, накладываемых одновременно на несколько столбцов, а также условия, накладываемые на вычисляемое формулой значение.

· Для объединения критериев с помощью логического И нужно указать задаваемые критерии в одной строке, а для объединения критериев с помощью логического ИЛИ следует представить критерии в разных строках. Следует также учитывать, что в случае необходимости вместе с критерием в ячейку надлежит ввести оператор сравнения. Для обозначения точного соответствия поля записи заданному критерию при задании критериев знак равенства (=) не используется.

3. Выходной диапазон.Область, в которую Excel копирует выбранные из таблицы данные. Этот диапазон должен быть расположен на том же листе, что и исходный. Извлеченную информацию можно поместить на другой лист копированием результата фильтрации. Задание выходного диапазона необязательно, т. к. существует опция “Фильтровать список на месте”. В этом случае фильтрация преобразует исходную таблицу в набор записей, удовлетворяющих условиям выбора.

Диапазон условий и выходной диапазон рекомендуется отделять от исходного диапазона, хотя бы одной пустой строкой (если они будут располагаться ниже или выше исходного диапазона) или одним пустым столбцом (если они будут располагаться левее или правее исходного диапазона). Если же предполагается, что исходный диапазон со временем будет расширяться, то диапазон условий рекомендуется разместить выше списка.

Рассмотрим порядок выбора из таблицы Учет движения материалов на складах с помощью Расширенного фильтра материалов, у которых приход превысил величину 200, а остаток на конец месяца составил меньше чем 15.

1. Создайте копию листа Сортировка и назовите его Расш_фильтр.

2. Сформируйте диапазон условий, для чего скопируйте заголовки полей исходного диапазона, которые будут ключевыми при отборе записей, и заполните строки критериев:

· cкопируйте D2 в A10; F2 –вB10;

· в A11 запишите критерий: >200, а в В11 критерий: <15.

3. Сформируйте выходной диапазон, скопировав заголовки полей исходного диапазона, которые необходимо отобразить в выходном диапазоне:

· выделите и скопируйте в буфер диапазон A2:F2;

· вставьте его содержимое в А13:F13.

4. Установите курсор в любую ячейку исходного диапазона.

5. Выберите команду Данные►Фильтр►Расширенный фильтр. В диалоговом окне Расширенный фильтр (рис. 5.19) проделайте следующие действия:

· в группе Обработка выберите переключатель Скопироватьрезультат в другое место. В этом случае исходная таблица останется нетронутой, а отобранные записи будут помещены в выходной диапазон. (При выборе переключателя Фильтровать список на месте не удовлетворяющие критерию записи будут скрыты в исходной таблице);

· установите курсор в поле Исходный диапазон и введите ссылку на диапазон исходной таблицы, включая шапку (A2:F7). Ввод ссылок во всех полях данного диалогового окна лучше всего осуществлять путем выделения указателем мыши нужного диапазона;

· установите курсор в поле Диапазон условий и введите ссылку на диапазон условий (A10:В11);

· так как выбран переключатель Скопировать результат в другоеместо, перейдите к полю Поместить результат в диапазон и введите ссылку на выходной диапазон (А13:F20);

· установите флажок Только уникальные записи, если не хотите, чтобы одинаковые записи повторялись (будет выводиться только первая из всех, удовлетворяющих критерию, одинаковых записей); нажмите кнопку [ОК].

Результаты работы Расширенного фильтра сразу же отобразятся на рабочем листе (рис. 5.20). Причем, если в окне Расширенный фильтр установлен переключатель Фильтровать список на месте, то подобно Автофильтру команда Расширенный фильтр скроет все строки, которые не удовлетворяют фильтру и отметит номера отобранных строк синим цветом, а в строке состояния будет отображено число найденных записей.

Примечание. При каждом выполнении команды Расширенный фильтр Excel просматривает полный список, а не текущее множество ранее отфильтрованных строк. Вследствие этого не обязательно использовать команду Показать все перед изменением фильтра.

Выберите из таблицы Учет движения материалов на складах только данные о материалах, у которых остаток на конец месяца меньше 10 или больше 50.

Как формируются диапазоны условий в этом случае показано на рис.5.21.

Подведение итогов

Часто бывает необходимо посчитать промежуточныеиобщие итоги в таблице. При этом таблица должна быть отсортирована по столбцам, которые являются группировочными признаками. Команда Данные►Итоги добавляет строки промежуточных итогов для каждой группы элементов. Допускается использование различных функций (например, среднего значения, количества строк или пустых ячеек, стандартное отклонение и т.д.) для вычисления итогов в пределах каждой группы.

В диалоговом окне Промежуточные итоги поле При каждом изменениив используется для указания столбца, по которому следует сгруппировать данные для подведения итогов; поле Операцияпозволяет использовать различные функции; поле Добавить итоги по:позволяет отметить все поля, по которым будут подводиться итоги. Установка флажка Заменить текущие итоги позволяет заменить в таблице уже существующие итоги. Установленный флажок Конец страницы между группами автоматически вставляет конец страницы перед каждой группой данных, для которой вычисляются итоги. Флажок Итогипод даннымидает возможность поместить строки промежуточных и общих итогов под соответствующими данными. Кнопка [Убрать все]позволяет восстановить исходный вид экрана до подведения итогов.

Используя возможности по автоматическому подведению итогов, рассчитаем частные (по каждому складу) и общие итоги в таблице Учет движения материалов на складах.

1. Отсортируйте таблицу по столбцу № склада.

2. Выделите диапазон таблицы вместе с шапкой, т.е. A2:F7.

3. Откройте окно формирования итогов командой Данные►Итоги.

4. Заполните диалоговое окно как показано на рис. 5. 22 и нажмите [ОК].

В результате подведения итогов таблица примет вид, представленный на рис. 5.23.

Кроме итоговых строк программа сформировала структуру (см. слева от таблицы), которая согласована с группировкой данных для вычисления промежуточных и общих итогов. Знак «-» означает, что можно спрятать строки, относящиеся к группе, оставив только промежуточный или общий результат, (щелкнув по кнопке [-]). Кнопки , , также служат для управления отображением структуры. Например, кнопка оставляет в таблице только промежуточные и общие итоги, скрывая содержимое таблицы.