- •Раздел 1. Работа со списками (таблицами) как с базами данных 2
- •Раздел 2. Финансовые вычисления с использованием Excel 35
- •Раздел 1. Работа со списками (таблицами) как с базами данных Задание 1. Обеспечение контроля за вводимыми данными
- •Методические указания для выполнения задания 1
- •Задание 2. Обеспечение контроля правильности введенных ранее значений
- •Методические указания для выполнения задания 2
- •1. Удаление условий.
- •2. Задание условия.
- •3. Обнаружение значений, не отвечающих поставленным условиям.
- •Задание 3. Обеспечение контроля правильности введенных ранее значений с помощью условного форматирования
- •Методические указания для выполнения задания 3
- •Задание 4. Подведение итогов по каждой группе записей
- •Методические указания для выполнения задания 4
- •Задание 5. Технология выбора данных с помощью "Расширенного списка"
- •Методические указания для выполнения задания 5
- •Задание 6. Организация поиска информации с помощью Расширенного фильтра
- •Методические указания для выполнения задания 6
- •Функция суммесли.
- •Функция датазнач.
- •Функция левсимв.
- •Задание 7. Обработка данных с помощью Сводных таблиц
- •Методические указания для выполнения задания 7
- •1. Создание сводной таблицы. Общие положения.
- •2. Модификация сводной таблицы
- •3. Дополнительные вычисления в сводной таблице
- •4. Изменение структуры сводной таблицы.
- •5. Сводные таблицы и диаграммы. Построение диаграмм.
- •Задание 8. Консолидация данных
- •2. Использование сводных таблиц для консолидации.
- •Задание 9. Контрольное задание Задача 1.
- •Задача 2.
- •Методические указания для выполнения контрольного задания
- •Раздел 2. Финансовые вычисления с использованием Excel Задание 1. Решение задачи с помощью функции бз
- •Методические указания для решения задания 1
- •Задание 2. Решение задачи с помощью функции пз
- •Методические указания для решения задания 2
- •Задание 3. Решение задачи с помощью функции норма
- •Методические указания для решения задания 3
- •Задание 4. Решение задачи с помощью функции кпер
- •Методические указания для решения задания 4
- •Задание 5. Решение задачи с помощью функции пплат
- •Методические указания для решения задания 5
- •Задание 6. Решение задачи с помощью функций пплат, плпроц и оснплат
- •Методические указания для решения задания 6
- •Задание 8. Контрольное задание
Задание 4. Подведение итогов по каждой группе записей
1. Скопировать таблицу, созданную в задании 2 в новую Рабочую книгу.
2. Удалить условия, наложенные на значения.
3. Модифицировать структуру таблицы таким образом, чтобы она приняла следующий вид:
NN |
Фирма |
N счета |
Дата счета |
Сумма в счете |
Дата оплаты |
Сумма оплаты |
Долг |
|
|
|
|
|
|
|
|
4. Заполнить добавленные поля произвольными значениями.
5. Добавить в таблицу несколько записей таким образом, чтобы информация по каждому клиенту была представлена не менее чем в 3-х отдельных записях.
6. Используя процедуру подведения промежуточных итогов, определить общую сумму, на которую были заказаны товары каждым из клиентов компании.
7. Скопируйте созданную таблицу на второй лист.
8. Удалите итоги.
9. Подвести итоги относительно суммарных закупок по каждому месяцу, а внутри каждого месяца по клиентам.
10. Сохраните выполненное задание.
Методические указания для выполнения задания 4
Для автоматического подведения итогов необходимо:
1. Выделить нужную таблицу (список).
2. Отсортировать данные, указав в качестве критерия для сортировки поле, по значениям в котором в дальнейшем предполагается группировать записи таблицы.
3. Выполнить команду ДАННЫЕ / ИТОГИ.
4. В открывшемся диалоговом окне Промежуточные итоги (рис.1):
a. в поле При каждом изменении – название поля по значениям которого будут группироваться записи таблицы;
b. в поле Операция – функцию, которая будет использоваться для подведения итогов;
c. в поле Добавить итоги по указать поле (поля) значения в которых будут участвовать в вычислениях.
d. установить флажки в надписях Заменить текущие итоги и Итоги под данными.
Рис. 1. Диалоговое окно Промежуточные итоги
Каждое последующее выполнение данной процедуры будет приводить к замене старых итогов новыми. Если же необходимо подвести итоги, сохранив при этом предыдущие результаты, достаточно при выполнении процедуры снять флажок Заменить текущие итоги.
Для того, чтобы удалить итоги, достаточно в диалоговом окне Промежуточные итоги нажать на кнопку Убрать все.
Задание 5. Технология выбора данных с помощью "Расширенного списка"
1. Скопируйте таблицу созданную на Листе 1 в Задании 4 в новую рабочую книгу. Сохраните новую книгу с именем Задание 5 в своей рабочей папке.
2. Удалите итоги.
3. В ячейках расположенных правее основной таблицы создайте дополнительную таблицу, шапка которой должна содержать поля из основной таблицы, для ввода критерия отбора. Ниже, отступив несколько строк, создайте еще такую же таблицу, для отбора записей из основной таблицы.
4. В первую дополнительную таблицу введите условия отбора данных:
В столбце Фирма нужно установить критерий отбора записей относящиеся к фирме «Василек»;
В столбце «Дата счета» установить критерий отбора записей относящихся к периоду от 01.02.03 до 17.02.03.
В столбике «Долг» установить критерий отбора записей, если сумма долга меньше 0.
5. Осуществить отбор по каждому критерию отдельно, а затем отобрать данные, удовлетворяющие трем критериям одновременно.
6. Сохраните задание.