Файл: Методичка Excel.docx

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

1. Дайте определение формулы, функции, константы, оператора?

2. Какие типы операторов вы знаете?

3. Какие виды ссылок вы знаете?

4. Дайте определение массива, формулы массива?

5. Какие виды массивов вы знаете?

6. Дайте определение константы массива?

7. Какие константы вы знаете?

Лабораторная работа № 2 работа со сводными таблицами

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

С помощью отчета сводной таблицы можно:

• организовать запрос к большим массивам данных;

• подвести промежуточный итог в таблице;

• применить статистические функции к числовым данным, просуммировать данные по категориям и подкатегориям, а также создать дополнительные вычисления и формулы;

• фильтровать, сортировать, группировать и форматировать подмножества данных.

Основные понятия отчета сводной таблицы:

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

Поле строки – поле, которому соответствуют строка. Элементы, связанные с полем строки, обозначаются как подписи строк.

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

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

Итоговая функция – вычисление при объединении данных в отчете сводной таблицы, в таблице консолидации или же при вычислении итогов в списках или базах данных. Например, СУММ, СЧЕТ и СРЕДНЕЕ.

Исходные данные – это диапазон данных или таблица, которые используются для создания отчета сводной таблицы. Кроме этого они могут быть взяты из внешней базы данных (например, Microsoft Access).

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

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

Элемент – категория поля в отчетах сводной таблицы и сводной диаграммы. Например, поле «Месяц» будет включать такие элементы, как «Январь», «Февраль» и т. п.

Запрос – средство поиска записей, отвечающих на определенный вопрос о данных, хранящихся в источнике данных.

Список – набор строк, содержащий взаимосвязанные данные.

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

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

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

1. В окне открытого листа выделите любую ячейку в диапазоне данных;

2. Перейдите к вкладке «Вставка» и в группе «Таблицы» раскройте меню кнопки «Сводная таблица»;

Рис. 2.1 Меню «Вставка – «Сводная таблица»

3. В поле «Укажите, куда следует поместить отчет сводной таблицы» выберите пункт:

• «на новый лист» – для размещения отчета сводной таблицы на дополнительном листе в книге;

• «на существующий лист» – для размещения отчета сводной таблицы в том же листе, где находятся и исходные данные.

4. В поле «Диапазон» необходимо указать место расположения отчета сводной таблицы;

5. Закройте окно кнопкой «ОК».

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

6. На листе (новом или существующем) появится пустая сводная таблица в виде контура макета, а справа листа откроется область «Список полей сводной таблицы» для создания отчета. В нем можно вставлять в сводную таблицу новые поля, создавать макет и настраивать отчет;

Создание отчета сводной таблицы, используя таблицу данных.

Таблица 2.1.

Исходные данные

1. В окне открытого листа Excel выделите таблицу данных;

2. Перейдите к вкладке «Вставка» и в группе «Таблицы» раскройте меню кнопки «Сводная таблица»;

3. В списке команд выберите пункт «Сводная таблица»;

4. В окне «Создание сводной таблицы» в графе «Таблица или диапазон» отобразится имя используемой таблицы.

Рис. 2.3. Выбор диапазона

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

Рис. 2.4 Шаблон сводной таблицы

Примечание: В открывшемся окне вы видите поле отчета сводной таблицы и список полей сводной таблицы (справа).

6. Далее необходимо сформировать сводную таблицу. Для этого в списке полей сводной таблицы нужно выбрать необходимые параметры. В нашем примере нужно выбрать наименование товара и приход. Результат виден на рис. 2.5.

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

Рассмотрим создание сводной таблицы еще на одном примере.

Таблица 2.2.

Исходные данные