Коднянко В.А. Электронные таблицы Microsoft Excel

ИНФОРМАТИКА
Коднянко В.А.
Лабораторный практикум

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

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

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

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

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

Выделите таблицу и закладке Данные щелкните на кнопке Фильтр . При этом в строке наименований полей (колонок) справа появились ярлычки выбора .

Щелкните по ярлычку в ячейке Покупатель. Распахнется список, показанный на рис. 4.1. Этот список содержит наименования всех введенных нами покупателей и позволяет сделать выбор нужных нам покупателей. Щелкните в строку Выделить все чтобы снять отметки со всех отмеченных покупателей. Затем поставьте галочку в строку Альтаир, как показано на рис. 4.1. Нажмите кнопку ОК.

Рис. 4.1. Список для фильтрации покупателей

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

Filter3.png (12347 bytes)

Рис. 4.3. Фильтрация цен

Установите опцию И. Это означает, что следует отбирать все строки, когда (Цена >= 70) И (Цена <= 100), т. е. одновременно выполняется первое И второе условие. Закройте окно рис. 4.3 кнопкой ОК. Результат сложной фильтрации данных показан на рис. 4.4.

Рис. 4.4. Результат отбора сложным фильтром

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

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

Выделите все строки, кроме первой и последней. Отсортируйте диапазон по полю Покупатель в алфавитном порядке. Далее выполните команду Промежуточные итоги. Появится окно, показанное на рис. 4.5. Установите параметры так, как показано в этом окне и нажмите кнопку ОК.

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

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

Теперь таблица (см. рис. 4.6 ) предстанет в довольно сложном на первый взгляд виде. Однако спустя короткое время Вы станете хорошо понимать новую разметку таблицы.

Рис. 4.6. Таблица итогов

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

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

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

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

Средствами Excel эта задача решается очень просто. Выделите всю таблицу кроме первой строки и выполните команду Вставка/Сводная таблица. Далее нужно подтвердить выбранный диапазон ячеек таблицы и что сводную таблицу следует создать на новом листе (рис. 4.7).

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

После закрытия этого окна появится новое окно, фрагмент которого показан на рис. 4.8.

Рис. 4.8. Окно подготовки сводной таблицы

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

Рис. 4.9. Сводная таблица

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

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

Рис. 4.10. Таблица с двумя сводными суммами

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

Как зафиксировать таблицу по ячейке E4?

Как из общей таблицы отфильтровать покупки, относящиеся к покупателю Сибтяжмаш и сделанные им в период с 4 по 6 июля 2010 г.?

Как создать сводную таблицу Покупатели\Товары самых дорогих покупок, исходя из анализа поля стоимость с НДС?

Как из общей таблицы выполнить отбор покупок за 5 июля 2001 г. количеством не менее 80 и ценой не более 10 руб.? Вставьте получившуюся таблицу в отчет и отредактируйте ее так, чтобы она приняла вид, показанный в табл. 4.1. Опишите порядок редактирования.

Таблица 4.1

Продажи за июль 2010
Покупатель	Товар	Цена	Кол-во	Стоимость с НДС
...	...	...	...	...
...	...	...	...	...
...	...	...	...	...