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

Задание 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. Установите курсор мыши в пределах сводной таблицы и нажмите правую кнопку мыши. Появится контекстное меню. Многие его команды соответствуют кнопкам на панели инструментов.

Самостоятельная работа 13. Использование сводной таблицы.

На листе Лаб.13 книги Лабораторные работы.xlsx создайте список, содержащий данные за два года о реализации продукции некоторой компанией, выпускающей мороженое. Для каждого из семи сортов мороженого (сливочное, шоколадное, ореховое, фисташковое, ванильное, кофейное, земляничное) в списке содержится информация о его реализации по регионам в каждом квартале 2016-17гг. Реализация фиксируется (рис.13.14) как в натуральном (шт.), так и стоимостном выражении (руб.).

Создайте справочную таблицу для определения цены за единицу продукции каждого сорта:

- на листе с именем Мороженое создайте таблицу с наименованиями всех семи сортов мороженого и ценой за единицу;

- присвойте полученным значениям соответствующие имена;

- используйте эту таблицу при расчете Общего объема продаж (руб.).

Рис.13.14. Исходные данные по реализации мороженного

 

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

Используя команду Данные, Сводная таблица, создайте сводную таблицу (рис.13.15) и поместите ее на отдельном листе с именем св.табл.

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

Задание 2. Модификация сводной таблицы.

1. Сделайте копию сводной таблицы в этой же книге, она автоматически получит имя св.табл (2).

2. Измените внешний вид сводной таблицы, переместив заголовки из области столбцов в область строк и наоборот (рис.13.16).

Рис.13.16. Вид сводной таблицы 2

3. Сделайте копию 1 сводной таблицы – св.табл (3) в этой же книге и переместите в область Фильтры поле Год. Отобразите данные за один год, выбранный в области Фильтры (рис13.16).

Рис.13.16. Вид сводной таблицы 3

4. Сделайте копию 3 сводной таблицы – св.табл (4) в этой же книге и поместите в область Фильтры поле Год и поле Квартал и отобразите данные за 2 квартал 2016 года.

5. Сделайте копию 1 сводной таблицы – св.табл (5) в этой же книге и добавьте в область Значения поле Объем продаж (шт.).

Задание 3. Работа с данными в сводных таблицах.

1. Отсортируйте данные в св.таб (3) по полю Сорт (по возрастанию) и Регион (по убыванию).

2. На листе св.таб скройте следующие столбцы: 2016 Итог, 2017 Итог, Общий итог.

Задание 4. Использование сводной таблицы для создания диаграммы.

1. На отдельном листе создайте диаграмму продаж ванильного мороженого по регионам на основе листа св.таб.

2. Подпишите горизонтальную ось: Кварталы (2016-2017гг.), подпишите название диаграммы: Продажа ванильного мороженого по регионам.

3. На диаграмме отобразите данные за 3 и 4 квартал 2017 года.

Вопросы для самоконтроля

1. Для чего применяются сводные таблицы?

2. Где можно расположить сводную таблицу?

3. Какие области существуют для расположения полей сводной таблицы?

4. Можно ли фильтровать данные в сводной таблице?

5. Можно ли создать сводную диаграмму по сводной таблице?

 

 

Оглавление

 

ПРЕДИСЛОВИЕ.. 3 ГЛАВА 1. ЗНАКОМСТВО С MS EXCEL 2013. 6

1.1. Назначение. 6

1.2. Элементы интерфейса. 6

1.2.1.Лента. 6

1.2.2.Вкладки. 8

1.2.3.Панель быстрого доступа. 9

1.2.4.Мини-панель инструментов. 10

1.2.5.Строка состояния. 11

1.3. Структура документа. 12

1.4. Выделение элементов рабочей книги Excel 15

1.4.1.Выделение листов. 15

1.4.2.Выделение ячеек и диапазонов ячеек. 16

1.4.3.Выделение столбцов и строк. 18

1.5. Режимы просмотра листов. 19

1.6. Изменение масштаба отображения листа. 20

1.7. Разделение листа. 21

1.8. Закрепление областей листа. 22

1.9. Скрытие и отображение столбцов и строк. 24

1.10. Перемещение по листу. 25

Вопросы для самоконтроля. 27

Тесты.. 28

ГЛАВА 2. ВВОД И ФОРМАТИРОВАНИЕ ДАННЫХ.. 33

2.1. Ввод данных. 33

2.2. Замена содержимого ячейки. 35

2.3. Редактирование содержимого ячейки. 35

2.4. Форматирование данных. 36

2.5. Способы выравнивания данных в ячейках. 40

2.6. Команды отображения данных. 44

2.7. Форматирование таблицы.. 47

Самостоятельная работа 1. Применение выравнивания, отображения текста. 51

Самостоятельная работа 2. Применение выравнивания, отображения текста. 52

Вопросы для самоконтроля. 53

Тесты.. 54

ГЛАВА 3. ЗАЩИТА ИНФОРМАЦИИ, ЧИСЛОВЫЕ ФОРМАТЫ И ОПЕРАТОРЫ EXCEL 58

3.1. Защита информации. 58

3.2. Форматирование чисел. 64

3.3. Ввод даты и времени. 69

3.4. Операторы ссылок. 72

3.4.1. Оператор диапазона. 73

3.4.2. Оператор объединения. 75

3.4.3. Оператор пересечения. 75

Самостоятельная работа 3. Использование форматов. 76

Контрольные вопросы.. 78

Тесты.. 78

ГЛАВА 4. АВТОМАТИЧЕСКИЙ ВВОД ДАННЫХ.. 82

4.1. Использование автозавершения. 82

4.2. Использование стандартных списков. 83

4.3. Создание пользовательских списков. 85

4.4. Использование диалогового окна Прогрессия. 89

4.5. Копирование и перемещение данных и листов. 91

Самостоятельная работа 4. Создание последовательности числовых рядов. 93

Вопросы для самоконтроля. 94

Тесты.. 95

ГЛАВА 5. ПРИСВОЕНИЕ ИМЕН.. 100

5.1.Правила создания имен. 100

5.2.Способы создания имен. 101

Самостоятельная работа 5. Создание имен. 108

Вопросы для самоконтроля. 111

Тесты.. 111

ГЛАВА 6. РАБОТА С ФОРМУЛАМИ.. 113

6.1. Структура формулы.. 113

6.2. Порядок выполнения действий. 115

Вопросы для самоконтроля. 119

Тесты.. 119

ГЛАВА 7. ФУНКЦИИ.. 122

7.1. Математические функции. 123

7.1.1. Суммирование. 123

7.1.2. Умножение. 127

7.1.3. Выборочная сумма. 128

7.1.4. Округление. 129

7.1.5. Возведение в степень. 131

7.2. Статистические функции. 131

7.2.1. Нахождение крайних значений. 132

7.2.2. Расчет средних значений. 134

7.2.3. Расчет количества ячеек. 135

7.3. Логические функции. 138

7.3.1. Функция ЕСЛИ() 139

7.3.2. Функция НЕ() 141

7.3.3. Функция И() 142

7.3.4. Функция ИЛИ() 144

Самостоятельная работа 7. Применение математических и статистических функций. 147

Вопросы для самоконтроля. 149

Тесты.. 150

ГЛАВА 8. ВИДЫ АДРЕСАЦИИ.. 154

8.1. Относительные ссылки. 155

8.2. Абсолютные ссылки. 157

8.3. Смешанные ссылки. 162

Самостоятельная работа 8. Применение относительной, абсолютной и смешанной адресации. 164

Вопросы для самоконтроля. 168

Тесты.. 168

ГЛАВА 9. ПОСТРОЕНИЕ ДИАГРАММ... 171

9.1. Гистограмма. 172

9.2. Круговая. 174

9.3. Линейчатая. 176

9.4. График. 177

9.5. Точечная. 178

9.6. Поверхность. 180

9.7.Комбинированная. 182

Самостоятельная работа 9. Построение диаграмм. 183

Вопросы для самоконтроля. 185

Тесты.. 185

ГЛАВА 10. АНАЛИЗ ДАННЫХ.. 192

10.1. Подбор параметра. 192

10.2. Таблица данных с одной переменной. 195

10.3. Таблица данных с двумя переменными. 198

10.4. Использование средства Диспетчер сценариев. 200

Самостоятельная работа 10. Использование в расчетах механизмов анализа данных. 203

Вопросы для самоконтроля. 209

Тесты.. 209

ГЛАВА 11. ВИДЫ ОШИБОК В EXCEL. ПРОЕКТИРОВАНИЕ РАСЧЕТОВ НА РАБОЧЕМ ЛИСТЕ 210

11.1. Типы ошибок в Excel 210

11.2. Вычисления в Excel 211

Самостоятельная работа 11. Проектирование расчетов на рабочем листе. 216

Вопросы для самоконтроля. 218

Тесты.. 218

ГЛАВА 12. ОБЪЕДИНЕНИЕ ДАННЫХ.. 222

12.1. Консолидация данных. 222

12.2. Способы объединения данных. 226

Самостоятельная работа 12. Использование консолидации. 229

Вопросы для самоконтроля. 231

Тесты.. 231

ГЛАВА 13. СОРТИРОВКА, ФИЛЬТРАЦИЯ, ПРОМЕЖЕТОЧНЫЕ ИТОГИ, СВОДНЫЕ ТАБЛИЦЫ 233

3.1. Сортировка. 233

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

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

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

Самостоятельная работа 13. Использование сводной таблицы. 245

Литература. 249 Оглавление. 250