6. СВОДНЫЕ ТАБЛИЦЫ

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

Данные в таблицу можно фильтровать. Для этого одна из ячеек таблицы должна быть активной и на ленте «Данные» необходимо выбрать команду «Фильтр» (рис. 130).

67

Рис. 130

Заголовки столбцов трансформируются в раскрывающиеся списки (рис. 131). Фильтровать можно по любому столбцу или их комбинировать. Например, ассортимент Булочной № 1 представлен на рис. 132, а черный хлеб, продаваемый в этом же магазине, на рис. 133.

Рис. 131

Снимаются фильтры, так же как и выставлялись, кликом мыши на команде «Фильтр».

Данные в таблице можно сортировать. Одна из ячеек таблицы должна быть активной.

Рис. 132

68

Затем на ленте «Данные» выполнить команду «сортировка» (рис. 134). Операция сортировки может иметь несколько уровней. Добавляются и удаляются они при нажатии на соответствующие команды в открывшемся диалоговом окне. Чем выше уровень, тем он приоритетнее. Выполним задачу сортировки названий магазинов по алфавиту, а затем цены изделия по возрастанию (рис. 135). Результат сортировки представлен на рис. 136. Строки в таблице при этом переставляются.

Рис. 133

Рис. 134

69

Рис. 135

Рис. 136

В Excel имеется возможность работы не с самой таблицей, а с ее формой, что достаточно удобно и безопасно (меньше ошибок при вводе), особенно для больших таблиц. Настроим панель быстрого доступа (обычно расположена вверху листа возле кнопки Offis) добавив на нее инструмент «Форма». Для этого выполним серию команд: «Offis» → «Параметры Excel» (рис. 137) → «Настройка» (рис. 138) →выбрать команду из («все команды») → «Форма» → «Добавить» (рис. 139)→команда появится в правой части диалогового окна,

70

изображенного на рис. 139, а после сохранения настроек она появится и на панели быстрого доступа (рис. 140-141).

Рис. 137

Рис. 138

71

Рис. 139

Рис. 140

72

Рис. 141

Воспользуемся добавленным инструментом «Форма». Активной должна быть любая ячейка таблицы с данными, далее выбираем команду «Форма» на панели быстрого доступа, появится диалоговое окно (рис. 142).

В этом окне записи (строки) таблицы можно листать по одной вверх («Назад») или вниз («Далее»), быстро с помощь линейки прокрутки, править прямо в форме.

Рис. 142

73

Рис. 143

Запись может быть выбрана с помощью заданного критерия. Кликнув по команде «Критерий» в появившейся пустой форме заполнить нужные поля. Например, пусть требуется выбирать только черные виды хлеба (рис. 141). Критерий может быть комбинированным (например, черный хлеб в Булочной №1 (рис. 144).

Рис. 144

74

Далее при листании будут выбираться только те записи, которые удовлетворяют заданному критерию. С помощью «Формы» можно удалить ненужную запись (требуется ее предварительно найти), или добавить новую запись при нажатии на соответствующие кнопки (Удалить/Добавить) диалогового окна. Если запись добавляется, появится пустая форма, которая и должна быть заполнена пользователем и вновь нажата команда «Добавить».

Чтобы создать сводную таблицу нужно сделать активной любую ячейку в таблице с исходными данными и на лете «Вставка» выбрать команду «Сводная таблица», рис. 145.

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

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

Рис. 145

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

75

Рис. 146

Устанавливаем переключатель, куда поместить сводную таблицу, например, «на новый лист» и нажимаем «Ок». На новом листе появляется трафарет для создания сводной таблицы, рис. 148. Далее наименования полей (столбцов исходной таблицы) необходимо поместить в выбранную область трафарета («названия строк», «названия столбцов», «значения», «фильтр отчета»). Выбирая области трафарета нужно стремиться к цели – результат анализа исходных данных, который мы хотим представить в создаваемой сводной таблице, должен быть максимально презентабельным, т.е. понятным и легко воспринимаемым.

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

76

Рис. 147

В сводной таблице на рис. 149 отображено количество по полю количество, т.е. анализируется, сколько раз данный хлеб заказывал конкретный магазин. Если необходимо чтобы анализировалось, сколько штук данного изделия заказывал конкретный магазин, то из контекстного меню поля количество (рис. 150) нужно выбрать команду «параметры полей значений» (рис. 150) и затем выбрать операцию «сумма» (рис. 151). Окончательный результат представлен на рис. 152. На рис. 153-154 – еще и отфильтровано по черному хлебу.

Рис. 148

77

Рис. 149

Рис. 150

78

Рис. 151

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

Рис. 152

79

Рис. 153

Рис. 154

Рис. 155

80

Диаграммы можно строить по сводной таблице. Для этого уберем строки с подведением итогов по строке и столбцу в сводной таблице. Из контекстного меню сводной таблицы выбрать команду «параметры сводной таблицы» (рис. 156).

Во вкладке «итоги и фильтры» в появившемся диалоговом окне снять выделение с полей «показывать общие итоги для строк» «показывать общие итоги для столбцов» (рис. 157).

Рис. 156

Рис. 157

81

Результат представлен на рис. 158.

Рис. 158

Сводная таблица должна быть активной, и далее на ленте «работа со сводными таблицами» на вкладке «параметры» выбираем команду «Сводная диаграмма» (рис. 159).

Можно и на ленте «Вставка» выбрать гистограмму. На рис. 160 изображено, например, как продавался черный хлеб во всех магазинах города.

Рис. 159

Рис. 160

82