- •Информатика
- •Часть 3 Разработка и анализ экономических документов с помощью электронных таблиц
- •Введение
- •Лекция 1. Электронные таблицы – современный инструмент компьютерных расчетов
- •1. Таблицы и табличные процессоры
- •2. Основные понятия электронных таблиц
- •Применение электронных таблиц для решения задач экономического содержания
- •Лабораторные занятия Занятие №1: Создание информационной среды для решения пользовательской задачи и ввод исходных данных
- •1. Информационная среда, предоставляемая программой ms Excel в распоряжение пользователя и ее настройка для решения пользовательских задач
- •2. Ввод данных и перемещение по листу
- •Занятие 2. Основные приемы редактирования и форматирования электронных документов с помощью Microsoft Excel
- •1. Особенности редактирования и форматирования электронных таблиц
- •Средства форматирования электронных таблиц
- •2.2. Копирование формата с помощью кнопки Формат по образцу
- •2.3. Форматирование чисел
- •2.4. Создание числового формата
- •2.5. Создание формата с помощью условного форматирования
- •Занятие 3. Особенности форматирования финансовых документов
- •1. Постановка задачи на разработку электронного документа и основные этапы его создания
- •2. Ввод и форматирование реквизитов документа
- •3. Использование абсолютной адресации ячеек
- •Вставка функций и графики, защита документа
- •Занятие 4. Средства анализа данных в электронных таблицах
- •1. Разработка документа для анализа
- •2.Оперирование данными. Сортировка данных
- •3. Поиск, фильтрация, редактирование в списках и базах данных
- •3.1. Автофильтрация
- •3.2. Расширенный фильтр
- •3.3. Фильтрация и помощью вычисляемого критерия
- •3.4. Поиск по критерию с помощью форм данных
- •3.5. Скрытие ненужных столбцов
- •4. Анализ документа с помощью диаграммы
- •Занятие 5. Таблица подстановки как средство решения задач экономического характера
- •1. Электронная таблица как динамическая модель
- •2. Таблица подстановки с одной ячейкой исходных данных
- •3. Таблица подстановки с двумя ячейками исходных данных
- •4. Диспетчер сценариев
- •Занятие 6. Использование команды специальной вставки для организации связей и вычислений в таблицах
- •1. Создание связей между таблицами
- •2. Вычисления с помощью специальной вставки
- •3. Использование макросов для вычислений
- •4. Консолидация данных
- •4.1. Консолидация по физическому расположению
- •4.2. Консолидация по заголовкам
- •Занятие 7. Поиск решения
- •1. Поиск решения
- •2.Подбор параметра
- •Занятие 8. Анализ и обработка данных с помощью сводных таблиц
- •1. Создание сводных таблиц
- •Самостоятельная работа
- •2.Изменение структуры таблицы
- •Вычисляемое поле
- •Самостоятельная работа
- •Обновление данных
- •Группирование элементов сводной таблицы.
- •Заключение
- •Литература:
1. Создание сводных таблиц
Сводные таблицы создают с помощью мастера.
Прежде, чем создавать сводные таблицы, следует построить базы данных в рабочей таблице.
Итак, пусть база данных имеет вид, представленный на рис.1.
Задание 1. Создание сводной таблицы
Дважды щелкните по ярлыку рабочего листа Лист1. Переименуйте его, назвав именем Исходные данные. Разместите на листе базу данных (рис.1).
Запустите мастер сводных таблиц, воспользовавшись командами: Данные, Сводная таблица. В результате появляется первое окно Мастера сводных таблиц, в котором следует выбрать источник данных. Поскольку в рассматриваемом примере источник данных находится в рабочей таблице, выберем в поле «Создать таблицу на основе данных, находящихся …» переключатель «в списке или базе данных Microsoft Excel».
После выбора источника данных следует нажать кнопку Далее. В следующем диалоговом окне мастера (рис.2) в поле Диапазон нужно указать диапазон ячеек, где располагается таблица исходных данных. Внимание! Если курсор находится внутри диапазона источника данных, то диапазон будет указан автоматически. После завершения второго шага в алгоритме построения сводных таблиц следует нажать кнопку Далее.
Рис.1. Исходная табличная база данных
Рис.2. Диалоговое окно мастера сводных таблиц
Шаг 3 заключается в определении места расположения сводной таблицы: на существующем листе или на новом и создании макета сводной таблицы. Выберите переключатель Новый лист и нажмите кнопку Макет. В диалоговом окне Макет (рис. 3) поля базы данных расположены в правой части макета в виде кнопок. Каждую из них следует разместить (перетащить с помощью «мыши») в одной их 4 областей макета сводной таблицы: область страниц, строк, столбцов, данных. При размещении кнопки поля в области страниц, строк, столбцов имена полей становятся заголовками строк, столбцов и страниц соответственно. При размещении кнопки поля в области данных мастер сводных таблиц использует формулу СУММ (SUM) по умолчанию, если значение поля является числом, и формулу СЧЕТ (COUNT), если поле содержит нечисловое значение. При определении макета сводной таблицы необязательно все поля должны быть размещены в указанных областях, в каждой области можно разместить одно или более полей. Если порядок размещения полей вам не понравился, вы можете вынести поле за пределы макета.
Рис.3. Макет сводной таблицы
Щелкните левой кнопкой манипулятора мышь по полю Наименование и удерживая кнопку мыши перетащите поле в область Страницы;
Щелкните левой кнопкой манипулятора мышь по полю Клиент и перетащите его в область Столбцы.
Щелкните левой кнопкой манипулятора мышь по полю Сотрудники и перетащите поле в область Строки.
Щелкните левой кнопкой манипулятора мышь по полю Количество и перетащите его в область Данные. В результате получится макет сводной таблицы, который представлен на рис.4. Нажмите кнопки ОК и Готово.
Рис.4. Мастер сводных таблиц после размещения на нем полей
В результате на новом листе разместится сводная таблица и будут открыты две новых панели инструментов Сводная таблица и Список полей сводной таблицы.
Щелкните дважды по ярлычку вновь появившегося рабочего листа. Переименуйте рабочий лист, выбрав имя Услуги БНТИ. Щелкните по раскрывающемуся списку значения поля страницы (все) и выберите Ксерокопирование. В результате получите сводную таблицу, представленную на рис.5. С помощью этой таблицы можно легко установить, каким клиентам и в каком объеме оказывались услуги по ксерокопированию, а также определить общий объем услуг по ксерокопированию.
Рис.5. Сводная таблица, содержащая услуги по ксерокопированию