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

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

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

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

Щелкните по ярлычку ячейки Покупатель, затем в списке – по строке Условие. В окне Пользовательский фильтр выберите условия так, как показано на рис 4.3.

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

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

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

Рис. 4.6. Окно установки итогов

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

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

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

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

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

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

Рис. 4.9. Окно третьего шага создания сводной таблицы

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

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

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

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

Если после приобретения этих знаний Вы расскажете о “волшебных” способносях Excel своим родителям и если они работают бухгалтером (счетоводом, замдекана, агрономом, завскладом и т. д.), то им, наверняка, это понравится и, не исключено, что они захотят приспособить (если еще не приспособили) эту “умную” программу для своих производственных нужд.

Вопросы к отчету о лабораторной работе № 4

1. Как зафиксировать таблицу по ячейке E4?
2. Как из общей таблицы отфильтровать покупки, относящиеся к покупателю Сибтяжмаш и сделанные им в период с 4 по 6 июля 2001 г.?
3. Как создать сводную таблицу Покупатели\Товары самых дорогих покупок, исходя из анализа поля стоимость с НДС?
4. Как создать сводную таблицу Товары\Даты средних цен купленных товаров?
5. Как из общей таблицы выполнить отбор покупок за 5 июля 2001 г. количеством не менее 80 и ценой не более 10 руб.? Вставьте получившуюся таблицу в отчет и отредактируйте ее так, чтобы она приняла вид, показанный в табл. 4.1. Опишите порядок редактирования.

Таблица 4.1