689

Задание 10. Расширенный фильтр

1Переименуйте лист "2 квартал" файла на "Расширенный фильтр".

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

Для задания диапазона условий выделите ячейки А4:G4 (заголовки столбцов), скопируйте и вставьте в ячейку К4 в соответствии с рисунком 2.25. Ячейки К4:Q4 – это заголовки новой таблицы, где будет формироваться критерий обора записей.

Рисунок 2.25. Формирование диапазона условий для расширенного фильтра

3 В ячейках К5: Q5 будут располагаться критерии (условия) отбора: Введите в ячейку N5 – условие ">24", в ячейку Q5 – ">1200".

4. Установите курсор в область исходных данных таблицы и выполните команду Дополнительно вкладки Данные. Откроется ДО Расширенный фильтр. Заполните поля данного диалогового окна в соответствии с рисунком 2.26. Нажмите кнопку ОК.

Рисунок 2.26. Настройка ДО Расширенный фильтр

62

Проверьте диапазон ячеек:

―В области Исходный диапазон должен быть указан диапазон А4: G14.. Если диапазон указан неверно, перейдите в область таблицы и выделите этот диапазон.

―В поле Диапазон условий укажите диапазон К4:Q5, выделив его

мышью.

―В поле Поместить результат в диапазон, укажите ячейку А17.

―Установите переключатель в строку скопировать результат в другое место.

―Установка флажка Только уникальные записи запрещает отображение или копирование записей-дубликатов.

―Нажмите кнопку Ок.

Врезультате должна получиться таблица соответствующая рисунку 2.27.

Рисунок 2.27. Результат расширенного фильтра

Примечания:

1По умолчанию установлен переключатель Фильтровать список на месте, и программа просто скроет записи, не удовлетворяющие условию фильтрации. Исходный вид таблицы восстанавливается командой Данные

/Фильтр

2Критерий сравнения может быть следующих типов: точного значения; значения формируемого с помощью операторов отношения; шаблона значения, включающего символы * и (или) ?

3Если критерии условия указываются в каждом столбце по одной строке, то считаются связанными условием И

4Если условия записаны в нескольких строках, то они считаются связанными условием ИЛИ.

63

5 Вычисляемый критерий представляет собой формулу, записанную в строке области условий, которая возвращает логическое условие Истина или Ложь.

Самостоятельно:

1Задайте расширенный фильтр со следующими критериями отбора: "цех 2" и начислено дивидендов - ">2000"

2Задайте расширенный фильтр со следующими критериями отбора: цех 2 или цех 1 и начислено дивидендов ">2500"

3Задайте расширенный фильтр со следующими критериями отбора: количество акций – "<40" и начислено дивидендов - ">1000"

4Задайте расширенный фильтр со следующими критериями отбора: количество акций – "<40" и начислено дивидендов - ">1000" или <2500

64

Задание 11. Подведение промежуточных итогов

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

Подведите промежуточные итоги по цехам в графах 4, 5, 6, 7:

1.Загрузите файл «СОРТИРОВКА». Выделите диапазон ячеек

В4:G14.

2.Выполните команду ДАННЫЕ / ПРОМЕЖУТОЧНЫЕ ИТОГИ (группа Структура).

Примечание. Если выделить диапазон с данными В5:G14, то по-

явится сообщение "Непосредственно над таблицей или базой данных обнаружена строка данных. Если эта строка содержит заголовки столбцов, ее необходимо включить в выделенный фрагмент, чтобы команда "Фильтр" работала правильно. Добавить строку в выделенный фрагмент?" и необхо-

димо ответить ДА

Рисунок 2.28. Информационное сообщение при выделении данных для подведения промежуточных итогов

В диалоговом окне Промежуточные итоги выполните настройку по образцу в соответствии с рисунком 2.29.

Результатом выполненных действий является появление итогов по цехам и общего по таблице.

Кроме того, программа создала области выше и/или левее таблицы, разместив в них кнопки управления структурой – символы структуры. Область группировки показана жирной линией, связанной с кнопкой, на которой изображен знак «минус». Эта кнопка позволяет скрыть группу данных. После того как данные скрыты, появляется кнопка «плюс», которая позволяет эти данные восстановить.

Если символы структуры на экране отсутствуют, следует выполнить ДАННЫЕ / ГРУППИРОВАТЬ / СОЗДАНИЕ СТРУКТУРЫ.

65

Задание 12. Связи между таблицами

1 Создайте новую книгу и создайте таблицу в соответствии с рисунком 2.31.

Рисунок 2.31. Таблица Прибыль

2Вычислите прибыль за 1 квартал как разницу между доходами и расходами.

3В столбец Стоимость 1 акции запишите формулу получения частного от деления прибыли на количество акций. Стоимость рассчитывать при условии, что предприятие имеет прибыль (т.е. число в столбце Прибыль/убытки > 0).

4Выделите ячейку, в которой будет рассчитываться стоимость и щелкните кнопку "fx" в строке формул или на вкладке Формулы кнопку «Вставить функцию».

5В окне «Мастер функций» (см. рисунок 2.30) выберите функцию ЕСЛИ категории Логические, нажмите ОК.

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

Вдиалоговом окне для функции ЕСЛИ в строке «Логиче-

ское_выражение» введите «D3>0», в строке «Значение_если_истина" введите

D3/E3, в строке “значение_если_ложь” введите 0, щелкните кнопку ОК.

Смотрите образец заполнения функции ЕСЛИ на рисунке 2.33.

Рисунок 2.32. Мастер функций

67

10 В ячейке Е3 таблицы ПРИБЫЛЬ должно появиться Количество акций всех акционеров, а в ячейке D3 – рассчитанная стоимость акции. Проверьте работу функции ЕСЛИ, увеличивая или уменьшая сумму расходов.

Рисунок 2.35. Установление связи между таблицами командой Специальная вставка

11 Заполните самостоятельно столбцы Доходы и Расходы для строк 2, 3, 4 кварталов, введя произвольные данные.

Скопируйте формулы расчета Прибыли, Стоимости 1 Акций и Количество акций в таблице Прибыль для 2, 3 и 4 кварталов.

12 Проверьте работу связи.

Измените, например, у Валетова С.Т. количество акций 50 на 150. Наблюдайте изменения в расчетах обеих таблиц.

Верните исходное количество акций.

13 Установите вторую связь (ссылку) для формулы начисления дивидендов.

―установите курсор в ячейку E5, введите знак “=”, щелкните по ячейке D5, введите символ умножения "*",

―перейдите в окно таблицы ПРИБЫЛЬ и щелкните по ячейке F3 (со стоимостью 1 акции 1 квартала). Наблюдайте как автоматически напишется формула =D5*[ПРИБЫЛЬ]лист1!$F$4, нажмите Enter. Сумма дивидендов у акционера должна измениться.

69

Примечание. В квадратных скобках указывается имя рабочей книги (т.е. файла). Восклицательный знак ставится после имени листа. Знак $ указывает на абсолютный адрес ячейки.

Рисунок 2.36. Связь между таблицами

14 Выполните пересчет сумм дивидендов у всех акционеров.

Для пересчета сумм дивидендов у всех акционеров скопируйте новую формулу (применив метод Автозаполнения – копирования) на весь список акционеров. Наблюдайте изменения в расчетах.

ВНИМАНИЕ! ИТОГ ПО НАЧИСЛЕНИЮ ДИВИДЕНДОВ В ФАЙЛЕ ДИВИДЕНДЫ ВСЕГДА ДОЛЖЕН РАВНЯТЬСЯ ПРИБЫЛИ В ФАЙЛЕ ПРИБЫЛЬ

15Измените формат числа для значений в ячейках E5:G13

16Проверьте работу второй связи. Измените значения дохода и расхода в таблице ПРИБЫЛЬ. Наблюдайте изменения в расчетах в обеих таблицах.

Верните исходные значения дохода и расхода

17Самостоятельно.

Откройте файл Сортировка. Сделайте пересчет дивидендов для 1 – 4 кварталов в файле Сортировка с учетом реальной стоимости 1 акции за 1 – 4 кварталы.

18 Закройте файлы с сохранением

70