- •14 Microsoft Excel. Составление калькуляций
- •Лабораторная работа №1 Cоставление калькуляций заказов с помощью электронных таблиц excel.
- •Шаг 1. Подготовка основного текста калькуляции (лист Калькуляция)
- •Шаг 2. Выбор процессора
- •2.2. Установка элемента управления Поле со списком
- •Порядок установки элемента управления
- •2.3. Установка цены выбранного процессора
- •2.4.Установка формата «у.Е.» в ячейке цены процессора
- •Шаг 3. Выбор конфигурации памяти.
- •3.1. Подготовка рабочего листа Память.
- •3.2. Установка элемента управления Счетчик
- •3.3. Выбор нужной конфигурации памяти и ее цены.
- •Шаг 4. Выбор винчестера и монитора.
- •Шаг 5. Расчет стоимости компьютера.
- •Шаг 6. Расчет стоимости гарантии
- •6.1. Установка элемента управления Переключатель
- •6.2. Установка параметров элемента управления Переключатель
- •7.3. Определение стоимости доставки
- •Шаг 8. Расчет общей суммы стоимости компьютера
- •Шаг 9. Корректировка элементов управления
- •9.1. Изменение положения и размеров установленного элемента управления
- •9.2. Изменение количества наименований на прайс-листах:
- •Контрольное здание
- •Лабораторная работа №2 Работа с базами данных (списками) в Microsoft Excel
- •1. Когда данные становятся списками
- •2. Создание структуры данных, ввод данных
- •2.1. Создание рабочего листа базы данных
- •2.2. Вычисление суммы продаж в рублях
- •2.3. Вычисление общей суммы продаж
- •2.4. Создание авто фильтра
- •2.4. Создание промежуточных итогов
- •3. Выборка из базы данных
- •3.1. Структура выборки
- •3.2. Построение круговой диаграммы
- •4. Сводные таблицы
- •4.1. Создание сводных таблиц
- •Порядок построения сводной таблицы:
- •4.2. Изменения в сводной таблице
- •4.2.1.Изменения данных в основном листе Продажи.
- •4.2.2. Изменение структуры сводной таблицы
- •4.3. Добавление нового элемента
- •4.4. Изменение порядка столбцов
- •4.5. Форматирование сводной таблицы.
- •4.6. Построение диаграмм сводных таблиц.
- •После этого постройте гистограмму используя Мастер диаграмм. Один из возможных вариантов такой гистограммы приведен на рис.12.
- •4.7. Редактирование диаграмм
- •Литература.
3. Выборка из базы данных
3.1. Структура выборки
Работать с автофильтром легко и просто, но если требуется постоянно видеть отфильтрованный список, то удобнее сделать выборку из базы данных, которая будет автоматически «перевычисляться» при добавлении новой информации.
Для создания такой выборки будем использовать функцию БДСУММ для работы с базами данных (категория «Работа с базами данных»). Эта функция выполняет операции выборочно, т.е. только с данными, соответствующими заданным критериям.
Формат функции:
БДСУММ(<база данных>;<имя поля>;<критерий выбора>)
<база данных> - диапазон ячеек базы данных (в нашем случае – А8:Е100);
<имя поля> - определяет столбец (поле) базы данных, над которым выполняется операция. Параметр < имя поля> задается как текст с названием столбца (поля) в двойных кавычках или как номер столбца. При этом надо помнить, что первый столбец диапазона базы данных имеет номер 1;
<критерий> - ссылка на диапазон ячеек, задающих критерий выбора строк (записей) в базе данных. Чтобы задать условия отбора для отдельного столбца, например для столбца «Менеджер», введите условия в ячейки, расположенные в смежных строках. Например, для следующего диапазона условий
Менеджер |
Иванов И.И. |
будут отобраны строки, содержащие Иванов И.И. в столбце Менеджер.
Пример 3.1. Подготовить информацию об объемах продаж, каждого менеджера за 1-ый квартал, чтобы иметь возможность сравнивать результаты их работы. Порядок выполнения
-
Выберите новый лист. Назовите его Менеджиры. Заполните блок ячеек А1:С4, как показано на рис.5.
-
В ячейку А5 введите формулу для вычисления суммы продаж Иванова И.И. Для этого активизируйте ячейку А5 и с помощью Мастера функций выберите функцию БДСУММ (из категории Работа с базой данных). В появившемся окне функции задайте параметры:
-
в поле База_данных - введите диапазон ячеек $А8:$Е100 листа Продажи;
-
в поле Имя поля - введите текст «Сумма,у.е.»;
-
в поле Критерий – введите диапазон ячеек А3:А4 листа Менеджеры (это блок критериев);
-
нажмите кнопку ОК.
Таким образом, в ячейке А5 записана формула:
А5= БДСУММ(Продажи!$A$8:$E$100;”Сумма,у.е.”;$A3:A4)
Если вы все проделали правильно, то в ячейке А5 запишется результат всех продаж менеджера Иванова И.И. Скопируйте формулу из ячейки А5 в ячейки В5:С5. Созданная выборка имеет вид, как показано на рис.5.
Теперь добавьте новые записи в базу данных на листе Продажи и посмотрите, как будет меняться выборка на листе Менеджеры, как будут меняться общие и промежуточные итоги на листе Продажи.
П
Внимание!
Изменения данных или добавление новых
записей на листе Продажи автоматически
отражаются на листе Менеджеры.
Контрольное задание 1. Добавить новые записи в базу данных, ввести изменения о продажах на листе Продажи и проанализировать изменения на листе Менеджеры.
3.2. Построение круговой диаграммы
Круговая диаграмма показывает абсолютную величину каждого элемента данных, и его вклад в общую сумму.
Пример 3.2. Построить диаграмму, отображающую долю каждого менеджера в общем объеме продаж фирмы «Старая Пермь».
Выберите лист Менеджеры (рис.5). Выделите диапазон ячеек А4:С5.
Нажмите кнопу Мастер диаграмм на панели инструментов. На экране появится диалог Мастер диаграмм (шаг 1 из 4):тип диаграммы.
Выберите Тип (Круговая) и Вид диаграммы. Нажмите и удерживайте кнопку Просмотр результатов, чтобы увидеть, как будут отражаться ваши данные на разных вариантах диаграмм.
Переходя от шага к шагу по кнопке Далее, вы можете задать заголовки диаграммы, разместить легенду (условные обозначения), изменить подписи значений. Например, чтобы отобразить объем продаж каждого менеджера в процентах от общей суммы, установите флажок на пункте доля на вкладке Подписи данных (на шаге 3).
Один из вариантов диаграмм листа Менеджер приведен на рис.5.
Р
Внимание!
Любые изменения на листе Продажи
автоматически отражаются на диаграмме.
Контрольное задание 3.2. Подготовить информацию об объемах покупок каждой фирмой, сотрудничающей с фирмой «Старая Пермь».