Создание промежуточных итогов

Задание 2. Создать таблицу с итоговыми данными.

Ваши действия:

1. Отсортируйте таблицу по полю Точка.

2. Установите курсор в любое место таблицы и выполните команду Данные, Структура, Промежуточный итог (рис.13.5).

Рис.13.5. Диалоговое окно Промежуточные итоги

3. Не меняя условия, сделанные программой нажмите на клавишу ОК.

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

Рис.13.6. Таблица с промежуточными итогами по полю Точка

Обратите внимание, что применяется функция Промежуточные итоги, где цифра 9 указывает на номер функции для вычисления промежуточных итогов - это сумма (Существует 11 разновидностей функций для вычисления Промежуточных итогов)

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

Область группировки показана жирной линией, связанной с кнопкой, на которой изображен знак минус. Эта кнопка позволяет скрыть группу данных. Каждый элемент группы отмечается точкой. После того, как данные скрыты, появляется кнопка со знаком плюс, которая позволяет легко их восстановить.

Кроме того, к символам структуры относятся кнопки, обозначающие уровни структуры. На этих кнопках изображен номер уровня. Простейшая структура имеет два уровня, а максимальное число уровней - восемь.

4. Отсортируйте таблицу по полю Наименование.

5. Выберите команду Данные, Структура, Промежуточный итог и создайте таблицу промежуточных итогов по этому полю (рис.13.7).

Рис.13.7. Таблица с промежуточными итогами по полю Наименование

6. Удалите структуру с помощью команды Данные, Структура, Промежуточный итог, Убрать все.

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

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

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

Строки, отобранные при фильтрации, можно редактировать, форматировать, создавать на их основе диаграммы, выводить их на печать, не изменяя порядок строк и не перемещая их.

Строки можно выбрать:

- по одному или нескольким значениям одного или нескольких столбцов;

- по формату (по цвету ячейки, цвету текста);

- по условию (условие можно применять для числовых значений).

Ваши действия:

1. Отсортируйте таблицу с исходными данными. Для этого создайте в диалоговом окне Сортировка два уровня сортировки: первый – по полю Точка, второй – по полю Наименование.

2. Выполните команду Данные, Сортировка и фильтр, Фильтр. После установки фильтров в названиях столбцов таблицы появятся значки раскрывающих списков (рис.13.8).

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

Рис.13.8. Таблица с установленными фильтрами

3. Сделайте щелчок по значку раскрывающегося списка столбца, по которому производится отбор, например, в столбце Наименование.

4. Выделите только одно наименование – Ржаной. В результате фильтрации в таблице останется только три строки (рис.13.9).

Рис.13.9. Таблица с фильтром по полю Наименование

5. Снимите фильтрацию, выполнив команду Данные, Сортировка и фильтр, Очистить.

6. Назначьте новый фильтр по полю Количество, сделав щелчок по значку раскрывающегося списка столбца.

7. Выполните команду Числовые фильтры и установите критерий больше или равно 100. В результате в таблице останется пять строк, которые удовлетворяют заданному условию (рис.13.10).

 

Рис.13.10. Таблица с фильтром по полю Количество

8. Снимите фильтрацию, выполнив команду Данные, Сортировка и фильтр, Очистить.

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

Рис.13.11. Диалоговое окно для текстовых фильтров

10. Снимите фильтрацию, выполнив команду Данные, Сортировка и фильтр, Очистить.

Создание сводной таблицы

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

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

Если на вкладке Данные отсутствует команда Сводная таблица, выполните команду Файл, Параметры, Настроить ленту и добавьте эту команду на вкладку в новую группу команд.

Особенность построения сводных таблиц заключается в том, что он удовлетворительно работает только с однородными, т.е. хорошо структурированными данными (см. рис.13.1).

Ваши действия:

1. Установите курсор в любое место таблицы и выполните команду Данные, Сводная таблица. Появится диалоговое окно Создание сводной таблицы (рис.13.12).

Рис.13.12. Диалоговое окно создание сводной таблицы

2. Укажите создание сводной таблицы на существующем листе в ячейку А15.

3. Необходимо определить, как будет выглядеть сводная таблица. Для этого переместите мышью следующие поля:

- Точка – в область Фильтры;

- Наименование – в область Строки;

- Цена – в область Колонны;

- Сумма – в область Значения;

- Нажмите кнопку Далее.

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

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

5. Выбирая элемента из списка по полю Точка, просмотрите данные по каждой точке отдельно.

6. Воспользуйтесь кнопкой Мастер сводных таблиц на панели инструментов Сводные таблицы: вы сразу попадете на третий шаг. Поменяйте местами два поля Точка и Наименование и нажмите на клавишу Готово.

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

8. Внесите в исходную таблицу поправку: измените цену какого-нибудь товара, например, на ржаной хлеб в Булочной № 2.

9. Выделите столбец данных с ценой на этот товар в сводной таблице и нажмите на кнопку Обновить данные.

10. Установите курсор мыши в пределах сводной таблицы и нажмите правую кнопку мыши. Появится контекстное меню. Многие его команды соответствуют кнопкам на панели инструментов.