Файл: LR_9_Excel_СозданиеТабл_ОАО_ЗАО_ч2.doc

Лабораторная работа.
Обработка таблиц

Если таблица содержит достаточно большое количество данных, то часто возникает потребность отобрать и систематизировать отдельные данные по определенному признаку или набору признаков. Например, может потребоваться узнать сколько в созданной ранее таблице зафиксировано проданных товаров предприятию Альтаир за 4 и 5 июля и на какую сумму, или мы захотим систематизировать проданные товары по каждому предприятию в отдельности с простановкой сумм по каждому из них и общей суммой продаж. Таких потребностей может накопиться множество, и хотелось бы чтобы результат можно было получить быстро и в удобном виде. Excel имеет такие возможности. Они и являются темой настоящей работы.

Запустите Excel и откройте созданную на прошлой лабораторной работе книгу Продажи.xls.

4.1. Простой отбор данных

Сначала решим простую задачу. Показать в таблице только те товары, которые проданы предприятию Альтаир.

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

Щелкните по ярлычку в ячейке Покупатель. Распахнется список, показанный на рис. 4.1.

Этот список содержит наименования всех введенных нами покупателей и еще несколько строк, поэтому позволяет сделать выбор покупателя или покупателей по определенному правилу. Строки списка говорят сами за себя. Несколько непонятна строка Условие, ее мы разберем отдельно.

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

Щелкните в списке на строке Альтаир. Будет произведена фильтрация покупателей по полю Покупатель, его значению Альтаир. Результат фильтрации показан на рис. 4.2. В таблице показаны только те товары, которые проданы покупателю Альтаир.

4.2. Сложный отбор данных

Предположим, потребовалось отфильтровать из таблицы покупателей Берег и Сибтяжмаш, которые купили товары по цене от 10 до 40 руб. Щелкните по ярлычку ячейки Покупатель, затем в списке – по строке Условие. В окне Пользовательский фильтр выберите условия так, как показано на рис 4.3.

Установите опцию ИЛИ. Это означает, что следует отбирать все строки, относящиеся к обоим покупателям. Закройте окно рис. 4.3 кнопкой ОК.

Щелкните в таблице по ярлычку в ячейке Цена, в списке щелкните по строке Условие. Установите в окне Пользовательский фильтр параметры отбора так, как показано на рис. 4.4. Обратите внимание, что теперь использована опция И. Это значит, что нужны цены, которые одновременно больше или равны 10 и меньше или равны 40. Если бы мы установили опцию ИЛИ, т. е. потребовали раздельного выполнения условий, то в фильтрованный список попали бы, например, товары с ценами, которые меньше 10, т. к. они удовлетворяют условию меньшие или равно 40. Это привело бы к ошибке фильтрации. Теперь таблица предстанет в виде показанном на рис. 4.5.

4.3. Итоги по группам

Предположим, что теперь нам захотелось “подбить” суммарные итоги покупок по каждому покупателю в отдельности и получить общий итог. Если установлен режим автофильтра, то снимите галочку со строки Автофильтр. Эту строку можно увидеть на панели, вызвав команду Данные/Фильтр (если галочки нет, то режим уже снят). Теперь в таблице должны быть видны все проданные товары.

Выделите все строки, кроме первой. Отсортируйте диапазон по полю Покупатель командой Данные/Сортировка. Далее выполните команду Данные/Итоги (таблица перед исполнением этой команды должна быть по-прежнему выделена). Появится окно, показанное на рис. 4.6.

Выберите в верхнем списке значение Покупатель, в следующем – сумма. Еще ниже в списке Добавить итоги по: поставьте галочки напротив полей Стоимость, НДС, Стоимость с НДС, поскольку лишь по этим полям можно подводить итоги. На остальных полях галочек не должно быть. Закройте окно кнопкой ОК.

Теперь таблица предстанет в довольно сложном на первый взгляд виде. Однако спустя короткое время Вы станете хорошо понимать новую разметку таблицы. Ее верхняя часть показана на рис. 4.7, нижняя – на рис. 4.8.

О том, как устроена новая таблица вполне понятно – кроме введенных строк она теперь содержит еще желаемые строки с итогами по каждому покупателю и общий итог по выбранным полям. А что означают появившиеся слева обозначения в столбцах с заголовком “1 2 3”? Смысл их прост. Левая длинная “скобка” указывает выбранный диапазон товаров, средние скобки – диапазоны отдельных покупателей, точки столбца 3 – строки товаров отдельного покупателя. “Минус” на кнопках означает, что диапазон распахнут. Его можно закрыть этой кнопкой. После “захлопывания” на кнопке появляется “плюс”. Поупражняйтесь с кнопками, раскрывая и закрывая диапазоны. Попробуйте также щелкнуть по кнопкам с надписями “1 2 3”. Не правда ли, весьма удобный способ представления итоговых таблиц.

4.3. Сводные таблицы

Exсel имеет средство, с помощью которого можно на основании одной таблицы формировать различные сводные таблицы.

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

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

В окне первого шага нужно щелкнуть по опции в списке или базе данных Microsoft Excel и нажать кнопку Далее.

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

Окно третьего шага, которое показано на рис. 4.9, является ключевым. Оно укажет Мастеру какими данными следует наполнить таблицу. Справа показан список всех полей Вашей таблицы. Наведите курсор на кнопку Товар и перетащите ее в левый столбец формируемой таблицы, как показано на рис. 4.9. Точно также перетащите кнопку Дата в верхнюю строку, а кнопку Стоимость с НДС – в центр таблицы.

Дважды щелкните по перемещенной в таблицу кнопке с надписью Стоимость с НДС. Откроется окно Вычисление поля сводной таблицы, которое показано на рис. 4.10.

Выберите в нем операцию Сумма (обратите внимание на другие операции списка - они Вам еще потребуются при составлении отчета). Нажмите кнопку Формат и назначьте формат Финансовый, 2 знака в дробной части, обозначение – Нет (к суммам не нужно приписывать наименования).

Закройте окна третьего шага соответствующими кнопками.

В окне четвертого шага выберите опцию новый лист и нажмите кнопку Готово. Сводная таблица создана (рис. 4.11).

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

Сводная таблица может содержать данные по нескольким полям. В качестве примера на рис. 4.12 приведена таблица для сводных данных по полям Стоимость с НДС и НДС. Для ее создания на третьем шаге Мастера нужно просто перетащить еще одну кнопку с наименованием поля.