- •Раздел 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. Контрольное задание
Методические указания для выполнения задания 5
Для того, чтобы с помощью расширенного фильтра отобрать нужные записи необходимо:
1. Правее или ниже создать таблицу с критериями отбора нужных записей (диапазон условий). Такая таблица должна состоять минимум из 2-х строк. В первую заносятся заголовки полей исходного списка по которым будут задаваться условия. В последующих – указываются сами условия (см. рис.1). Обратите внимание на то, что если условия объединены оператором «И» (выполняются одновременно) они указываются в одной строке диапазона условий. Отсюда правило: если необходимо задать по одному и тому же полю несколько условий объединенных оператором «И», необходимо указать заголовок этого поля несколько раз.
Условия, объединенные оператором «ИЛИ» вводятся в отдельные строки диапазона условий.
В качестве условий могут быть использованы и формулы, позволяющие вычислить их значение.
2. Расширенный фильтр позволяет как сохранять результаты фильтрации в исходном списке, просто скрывая записи, не отвечающие поставленному условию, так и копировать результаты в отдельную таблицу. Второй вариант предпочтительнее, поскольку позволяет:
a. сопоставить результаты фильтрации и исходный список;
b. сопоставить результаты нескольких независимо проведенных фильтраций.
Для того, чтобы результат фильтрации находился отдельно от исходного списка, на данном этапе, необходимо создать таблицу, предназначенную для хранения выборке (диапазон вывода данных). Такая таблиц обязательно должна находиться на том же листе, что и исходная и состоять из заголовков полей выборку по которым требуется провести.
3. Выделить исходный список и выполнить команду Данные Þ Фильтр Þ Расширенный фильтр. В открывшемся диалоговом окне (рис.2) выбрать вариант обработки «Скопировать результат в другое место» после чего указать координаты Исходного диапазона, Диапазона условий и Диапазона вывода данных. При установке флажка «Только уникальные записи» повторяющиеся записи исходного списка в выборке будут встречаться только один раз.
Рис. 1. Диалоговое окно Расширенный фильтр
Задание 6. Организация поиска информации с помощью Расширенного фильтра
1. Создать таблицу, аналогичную приведенной на рис.1
№ |
Название фирмы |
Специализация |
№ счета |
Дата счета |
Сумма в счете |
№ плат. поручения |
Дата оплаты счета |
Сумма оплаты |
1 |
ООО «Пласт» |
Строительство |
62 |
16.02.04 |
72000 р. |
|
16.02.04 |
0 р. |
2 |
ООО «Пласт» |
Строительство |
62 |
16.02.04 |
0 р. |
1046 |
26.02.04 |
10050 р. |
3 |
ООО «Пласт» |
Строительство |
62 |
16.02.04 |
0 р. |
2046 |
26.02.04 |
50000 р. |
4 |
ООО «Меткий глаз» |
Оптика |
64 |
11.03.04 |
10000 р. |
|
11.03.04 |
0 р. |
5 |
ООО «Канцлер» |
Канцелярия |
69 |
18.04.04 |
11070 р. |
|
18.04.04 |
0 р. |
6 |
ООО «Красота» |
Косметика |
66 |
14.04.04 |
10030 р. |
|
14.04.04 |
0 р. |
7 |
ООО «Хозяюшка» |
Бытовая техника |
67 |
15.04.04 |
30040 р. |
|
15.04.04 |
0 р. |
8 |
ООО «Китайская ширма» |
Мебель |
68 |
17.04.04 |
15060 р. |
|
17.04.04 |
0 р. |
9 |
ООО «Канцлер» |
Канцелярия |
89 |
18.07.04 |
31700 р. |
|
18.02.04 |
0 р. |
10 |
ООО «Канцлер» |
Канцелярия |
89 |
18.07.04 |
0 р. |
1065 |
28.07.04 |
10000 р. |
11 |
ООО «Меткий глаз» |
Оптика |
64 |
11.03.04 |
0 р. |
1041 |
21.04.02 |
10000 р. |
12 |
ООО «Бартолет» |
Бытовая химия |
71 |
22.04.04 |
10110 р. |
|
22.04.04 |
0 р. |
13 |
ООО «Роза» |
Цветы |
65 |
13.04.04 |
0 р. |
1043 |
23.04.04 |
17020 р. |
14 |
ООО «Пласт» |
Строительство |
72 |
23.04.04 |
86120 р. |
|
23.04.04 |
0 р. |
15 |
ООО «Красота» |
Косметика |
66 |
14.04.04 |
0 р. |
1044 |
24.04.04 |
30040 р. |
16 |
ООО «Хозяюшка» |
Бытовая техника |
67 |
15.04.04 |
0 р. |
1045 |
25.04.04 |
15060 р. |
17 |
ООО «Китайская ширма» |
Мебель |
68 |
17.04.04 |
0 р. |
1047 |
27.04.04 |
11070 р. |
18 |
ООО «Канцлер» |
Канцелярия |
69 |
18.04.04 |
0 р. |
1048 |
28.04.04 |
10090 р. |
19 |
ООО «Ленюг» |
Обувь |
70 |
20.04.04 |
0 р. |
1050 |
30.04.04 |
10110 р. |
20 |
ООО «Бартолет» |
Бытовая химия |
71 |
22.04.04 |
0 р. |
1052 |
02.05.04 |
10110 р. |
2. Используя в расширенном фильтре вычисляемые условия:
отобрать сведения по фирмам, оплатившим счета в десятидневный срок.
отобрать сведения по фирмам, оплатившим счета после 01.05.2004.
отобрать сведения по фирмам, оплатившим после 01.04.2004 больше 20000 руб.
Критерии поиска и результаты фильтрации должны быть представлены в виде отдельных таблиц.
3. Сохраните выполненное задание.