Методические указания для выполнения задания 6

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

Рис. 2. Вычисляемый критерий для выбора сведений о счетах, оплаченных в 10-дневный срок

Рис. 3. Вычисляемый критерий для выбора сведений о фирмах с организациионнно-правовой формой «ООО», оплативших после 01/04/2002 больше 20000 руб.

Остановимся подробнее на функциях, которые были использованы для создания второго вычисляемого критерия:

Функция суммесли.

Суммирует значения в ячейках, заданных критерием. В нашем примере используется для определении общей суммы, выплаченной по каждому счету.

Синтаксис функции:

СУММЕСЛИ(интервал; критерий;сумм_интервал)

a.      Интервал — это интервал ячеек, значения в которых будут проверяться на предмет соответствия поставленному условию (в нашем примере – ячейки, содержащие сведения о номерах выставленных счетов). Ссылка на этот диапазон должна быть абсолютной.

b.     Критерий — условие, согласно которому будут отбираться записи, по которым будет вычисляться сумма (в нашем примере – адрес первой ячейки указанного диапазона; ссылка – относительная; такое построение условия позволяет добиться группировки записей таблицы по совпадению значений в указанном диапазоне).

c.     Сумм_интервал — диапазон ячеек, значения которого будут суммироваться (в нашем примере – сумма оплаты).

Функция датазнач.

Возвращает числовой формат даты, представленной в виде текста. Функция ДАТАЗНАЧ используется для преобразования даты из текстового представления в числовой формат.

Синтаксис функции:

ДАТАЗНАЧ(дата_как_текст)

Дата_как_текст — это текст, представляющий дату в формате даты Excel. Например, "1.30.1998" или "30 янв 1998" являются текстовыми строками в кавычках, которые представляют даты.

Функция левсимв.

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

Синтаксис функции:

ЛЕВСИМВ(текст;количество_символов)

a.      Текст — это текстовое значение, начальные которая содержит извлекаемые символы.

Количество_символов — определяет сколько символов необходимо извлечь.

Задание 7. Обработка данных с помощью Сводных таблиц

1.     Скопировать таблицу с исходными данными, созданную в задании 6 в новую Рабочую книгу.

2.     С использованием Мастера сводных таблиц создайте Сводную таблицу, позволяющую анализировать движение денежных средств (Сумма оплаты) и дебиторской задолженности (Сумма в счете) клиентов компании.

3.     В созданную Сводную таблицу введите Вычисляемое поле, в котором будет рассчитываться долг клиента (разница между суммой в счете и суммой оплаты) на каждый момент времени.

4.     Ввести группировку данных Сводной таблицы по временным диапазонам с шагом месяц.

5.     Для повышения наглядности задать наиболее приемлемый формат ячеек Сводной таблицы.

6.     Используя Дополнительные вычисления, обеспечить возможность просмотра на каждый момент времени суммарных (с начала анализируемого периода) данных.

7.     Скопировать Сводную таблицу на второй лист.

8.     Модифицировать сводную таблицу таким образом, чтобы можно было просматривать данные по счетам за каждый месяц в отдельности.

9.     На основе данных Сводной таблицы построить Сводную диаграмму.

10. Сохранить выполненное задание.