lab12

Упорядочить журнал по названиям филиалов. Подвести промежуточные итоги о суммах выручки за каждый квартал,итоги по филиалу и общий итог.

Для подведения поквартальных итогов необходимо вычислить номер квартала по дате. Номер квартала можно вычислить по номеру месяца данной даты как:

Номер_квартала = ОКРУГЛВВЕРХ( Номер_месяца / 3;0)

Номер месяца можно получить используя функцию =МЕСЯЦ():

Номер_месяца = МЕСЯЦ(Номер_квартала)

Для отображения номера месяца римскими цифрами используйте функцию =РИМСКОЕ().

Добавьте колонку квартал (колонка B), для вычисления квартала по дате платежа. Исключив промежуточные переменные Номер_месяца и Номер_квартала, в столбце B получим формулу для отображения номера квартала римскими цифрами:

=РИМСКОЕ(ОКРУГЛВВЕРХ(МЕСЯЦ(A2)/3;0))

Отсортируйте таблицу по кварталам и филиалам. Установите автофильтр.Подведите итоги по кварталам и филиалам.

По созданной таблице сформируйте несколько запросов с помощью команды Расширенный фильтр из меню Данные по образцам:

Выбрать из списка все записи, содержащие информацию о конкретном клиенте и покупках,

совершенных после заданного числа.

Для создания такого запроса нужно задать условия отбора на разных строках.

Вывести на экран список клиентов, которые приобрели определенный виды оборудования.

Купленная техника,ПО,оказанная услуга и др.

А

Р

С

Выбрать из списка все записи, содержащие

информацию об определенном виде проданного оборудования за 10.04.00.

Для формирования такого критерия нужно задать условия на одной строке

Отобрать из списка строки, содержащие информацию о клиентах, которые приобрели определенные виды товаров и имеют статус

постоянного клиента.

Условие «постоянный клиент» должно быть повторено на каждой строке диапазона критериев, так как заданы два условия:по алфавиту и по сумме.

Задание текстовых условий.

1Найти все значения по заданному полю, которые начинаются с М. Например, для поля «Купленная техника, ПО, оказанная услуга и др.» это условие выполнится для ячеек содержащих:Монитор,Мышь, Манипулятор,и т.п.

Купленная техника или ПО,оказанная услуга и.т д.

М

2 Если поставить символ >или< перед буквой, это означает,что нужно найти все значения по алфавиту соответственно после или перед введенным текстовым значением.

2 Если ввести формулу ="=текст", то можно найти значения, которые точно совпадают со строкой символов текст.Например:

Телефон

="=22-3467 "

3.Столбец А дополнительно можно оформить примечанием,например,внести реквизиты фирмы и т.д.(Меню Вставка,команда Примечание)

4.В столбцах В и С заполните по 10 ячеек (заполняются все ячейки непосредственно при заключении договора).

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

6.В ячейки столбца E введите формулу,использующую функцию ЕСЛИ(), которая должна автоматически рассчитывать сумму сделки с учетом скидки. Сумма скидки определяется по условиям:

а)если сумма превышает установленное значение Х, то скидка 3%

б) если сумма превышает установленное значение Y (Y>X),то скидка 5% .

Предельные значения Х и Y задать в ячейках J2 и J3,а в формулах для расчета скидки применить абсолютные ссылки на эти ячейки.Можно в ячейки K2 и K3 занести величины процентов скидки.

7.Дату переучета в ячейку F2 установите с помощью функции СЕГОДНЯ().При желании в эту ячейку можно вставить любую дату и автоматически получить информацию по состоянию дел на указанную дату.

8.Число просроченных дней в ячейке G 2 рассчитайте по формуле:=ЕСЛИ(D 2 = 0;$F$2 – B2;””).

Обязательно установите числовой формат для столбца G .Скопируйте полученную формулу до конца диапазона.

9.Для расчета количества просроченных платежей

свыше 20 дней в ячейку Н2 введите формулу =СЧЕТЕСЛИ(Е2:Е11;” >=20”). Эта функция вычислит количество непустых ячеек в указанном диапазоне, удовлетворяющих данному критерию. Заполните несколько пустых ячеек в столбце D , измените дату переучета или количество дней, сумма в ячейке Н11 будет меняться

10.В ячейке I2 вычислите стоимость просроченных платежей,используя функцию СУММЕСЛИ(): =СУММЕСЛИ(G 2:G 11;”>0”;E2:E11).

Задание 4

1.Скопируйте файл p:\studapp\info\oshkalo.vv\inform atika\svod.xls на свой пользовательский диск,загрузите его и преобразуйте его в список.

2.Выполните:Данные->Форма… ,добавьте в список данные об АО Престиж:"24.02.02,Материалы,10000 р.,АО Престиж"

3.Используя Форму данных… ,просмотрите информацию о Казакове К.Г и измените сумму зарплаты за 6.01.02

на 2800 р.

4.Используя форму данных,просмотрите все данные списка о расходах на материалы,превышающих

12000 р.

5.Выполните:Фильтр-> Автофильтр и отобразите все

данные списка по АО АО ИНВЕСТ.

6.Выполните:Фильтр-> Автофильтр и отобразите все данные списка по накладным расходам,а затем только за январь.

7.Выполните:Данные->Итоги и по каждому расходу подведите итог по полю Сумма (предварительно отмените Автофильтр и отсортируйте данные списка по полю Расход).

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

9.Выполните:Данные->Расширенный фильтр и:

10.Используя вычисляемый критерий рассчитайте сумму накладных расходов за январь.

11.Для исходного списка,применив функцию

БСЧЕТ(),подсчитайте в отдельной ячейке количество выданных зарплат,с помощью функций ДМАКС()и ДМИН()определите соответственно минимальную и максимальную выплаты.

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