(для определения значения коэффициента К необходимо использовать функцию ЕСЛИ);

2) суммарные значения остаточной, восстановительных полной и остаточной стоимостей основных средств производства (функция СУММ), среднее значение износа (функция СРЗНАЧ);

3) суммарную остаточную стоимость оборудования для каждого типа объектов (функция СУММЕСЛИ);

4) количество объектов, износ которых превысил 30 % от их балансовой стоимости (функция СЧЁТЕСЛИ).

3. С помощью фильтрации получите следующую информацию:

1) наименования объектов, остаточная стоимость оборудования которых менее 400 у. е.;

2) наименование объекта с максимальным износом оборудования;

3) наименования основных объектов производства с балансовой стоимостью менее 1000 у. е. и остаточной стоимостью менее 350 у. е.;

4) наименования вспомогательных объектов производства с восстановительной полной стоимостью менее 400 или более 800 у. е.

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

2.2. Вид таблицы с исходными данными

На пустом листе книги Microsoft Excel сформируем таблицу требуемой структуры. Получим таблицу с исходными данными:

2.3. Проведение расчета характеристик

Рассмотри проведение расчета характеристик для первой строки таблицы.

Для расчета ОС введем в ячейку E4 формулу: =C4-D4,  в ячейку F4 введем формулу: = ЕСЛИ(C4<=500;C4*2;C4*3), а в ячейку G4 введем формулу =ЕСЛИ(C4<=500;E4*2;E4*3). Размножим полученные формулы во все остальные строки таблицы при помощи Автозаполнения.

2.4. Вычисление итогов

Например, для подсчета суммы по столбцу ОС в ячейку E14 введем формулу =СУММ(E4:E13).

Аналогичные формулы будут для расчета суммарной ВПС и ВОС.

2.5. Вычисление среднего износа

В ячейку D15 введем формулу =СРЗНАЧ(D4:D13).

2.6. Определение остаточной стоимости оборудования каждого типа

Например, для подсчета Суммарной остаточной стоимости основных объектов введем в ячейку E16 формулу =СУММЕСЛИ(B4:B13;"Осн.";E4:E13).

Для подсчета Суммарной остаточной стоимости вспомогательных объектов введем в ячейку E17 формулу =СУММЕСЛИ(B4:B13;"Вспом.";E4:E13).

2.7. Определение количества объектов с износом более 30% от их балансовой стоимости

Перед этим добавим в таблицу столбец Процент износа после столбца Остаточная стоимость.

В ячейку F4 введем формулу =D4/C4*100. Аналогично в остальных строках таблицы.

Теперь в ячейку E18 введем формулу =СЧЁТЕСЛИ(F4:F13;">30").

2.8. Вид готовой таблицы

В Microsoft Excel готовая таблица имеет вид:

Или в режиме просмотра формул:

2.9. Применение фильтрации

2.9.1. Наименование объектов с остаточной стоимостью оборудования менее 400 у.е.

Для включения фильтра выполним пункт меню Microsoft Excel Данные > Фильтр > Автофильтр[1], находясь в одной из ячеек заголовков таблицы.

После этого из списка Автофильтра для столбца Остаточная стоимость выберем пункт Условие.

В открывшемся окне зададим параметры:

Будут отобраны записи:

2.9.2. Наименование объекта с максимальным износом оборудования

Для столбца Износ из списка автофильтра выберем максимальное значение. Получим объект с максимальным износом оборудования:

2.9.3. Наименование основных объектов производства с балансовой стоимостью менее 1000 у.е. и остаточной стоимостью менее 350 у.е.

Для столбца Тип объекта из списка автофильтра выберем пункт Осн. Для столбца БС зададим условие менее 1000, а для столбца ОС – условие менее 350. Получим следующие записи:

2.9.4. Вспомогательные объекты производства с восстановительной полной стоимостью менее 400 или более 800 у.е.

Для столбца ВПС зададим параметры автофильтра менее 400 ИЛИ более 800. Получим записи:

2.10. Создание сводной таблицы

Для создания сводной таблицы выполним пункт главного меню Microsoft Excel Данные>Сводная таблица.

На первом шаге мастера сводных таблиц и диаграмм зададим Создание таблицы на основе данных, находящихся в списке или базе данных Microsoft Excel, а вид создаваемого отчета – сводная таблица.

На втором шаге зададим диапазон A3:H13, содержащий исходные данные.

На следующем шаге нажмем кнопку Макет. В раздел Строка перенесем поле Тип объекта, в раздел Столбцы – Наименование объекта, а в раздел Данные – Восстановительная остаточная стоимость.

Получим следующую сводную таблицу:

Задание 3. СУБД Microsoft Access

3.1. Постановка задачи

1. Создать таблицы базы данных риэлтерской фирмы:

Агенты (главная таблица)

Первичным ключом является поле Код агента.

Квартиры (подчиненная таблица)

Внешним ключом таблицы является поле Агент.

2. Связать таблицы по ключевым полям Код агента – Агент.

3. С помощью запросов к базе данных определить:

         1) квартиры, расположенные в Южном районе;

         2) трехкомнатные квартиры, приватизированные до 1999 г;

         3) квартиры, расположенные в Южном районе или имеющие

    общую площадь от 40 до 60 квадратных метров;

4) среднюю жилую площадь квартир, расположенных в каждом

     районе;

5) список квартир, продажей которых занимается агент Сидоров.

4. Создать отчет по запросу к таблицам Агенты и Квартиры.

         Отчет должен включать:

1)    заголовок, характеризующий содержание отчета;

2)    данные полей таблиц ФИО, Район, Адрес, Количество комнат, Общая площадь, кв.м.;

3)    группировка строк отчета по районам расположения квартир;

4)    сортировку строк отчета по адресам квартир;

5)    суммарную площадь всех квартир;

6)    нижний колонтитул, содержащий фамилию и инициалы.

5. Создать пользовательскую форму для таблицы Квартиры.

3.2. Создание структуры таблиц и заполнение их данными

Таблицы базы данных состоят из полей, имеющих следующие типы данных:

В таблице Агенты ключевым полем является поле Код агента, таблица Квартиры имеет внешний ключ – поле Агент.

Между таблицами установлена следующая связь типа «один-ко-многим»:

Таблицы имеют следующие данные:

3.3. Создание запросов

3.3.1. Квартиры, расположенные в Южном районе

Запрос создадим при помощи Конструктора[2]. Перенесем все поля из таблицы Квартиры.

Для поля Район зададим условие отбора «Южный».

В Конструкторе получим:

По запросу получим данные:

3.3.2. Трехкомнатные квартиры, приватизированные до 1999 г.

В запросе будут поля Район, Адрес, Количество комнат, Жилая площадь, Общая площадь и Дата приватизации.

Для поля Количество комнат зададим условие отбора 3, а для поля Дата приватизации - <#01.01.1999#.

В Конструкторе запрос имеет вид:

По запросу получим:

3.3.3. Квартиры, расположенные в Южном районе или имеющие общую площадь от 40 до 60 квадратных метров

Для поля Район зададим условие отбора «Южный», а для  поля Общая площадь в разделе или зададим Between 40 And 60. В режиме Конструктора запрос имеет вид:

Выполнив этот запрос, получим:

3.3.4. Средняя жилая площадь квартир, расположенных в каждом районе

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

На странице Запросы главного окна базы данных нажмем кнопку Создать и выберем Перекрестный запрос.

Для отображения в запросе выберем поля из таблицы Квартиры. Для заголовков строк выберем поле Район, а для заголовков столбцов – поле Количество комнат.

Для поля Жилая площадь зададим вычисление среднего значения.

Запрос выдаст данные:

3.3.5 Список квартир, продажей которых занимается агент Сидоров

В запросе будут участвовать все поля таблицы Квартиры и поле ФИО из таблицы Агенты.

Для поля ФИО зададим условие отбора Like “Сидоров*”.

В Конструкторе получим:

По запросу получим:

3.4. Создание отчета

В отчете требуется  отобразить поля из обеих таблиц. Поэтому, перед созданием отчета, нужно создать простой запрос, который будет содержать необходимые поля.

Этот запрос выдаст результат:

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

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

Добавим уровень группировки по полю Район. Сортировку зададим по поля Адрес.

Нажав кнопку Итоги установим вычисление функции Sum[3] для поля Общая площадь.

Выберем макет отчета блок. Стиль выберем Строгий.

Созданный отчет при просмотре выдаст результат в виде:

3.5. Создание формы

На вкладке Формы окна базы данных нажмем кнопку Создать и выберем Мастер форм.

В качестве источника данных для формы выберем таблицу Квартиры. Выберем все поля из этой таблицы. Внешний вид формы выберем в один столбец[4]. Стиль выберем Наждачная бумага. Получим форму:

Задание 4. Информационно-поисковые системы

1. В информационной базе Консультант Плюс найдите следующие нормативно-правовые акты: Указания государственного таможенного комитета РФ по вопросам таможенных льгот международным организациям.

2. Сформируйте список найденных документов.

3. Средствами Консультант Плюс создайте пользовательскую папку  с именем, соответствующим Вашему варианту, и сохраните в ней найденные документы.

4. Откройте текст первого документа и установите закладку в начале второго абзаца.

Вводим соответствующие реквизиты в поля:

После  нажатия «Построить список» получаем следующий список документов:

Первый документ есть тот который требуется найти

Занесем его в созданную пользовательскую папку:

Сделаем закладку на втором абзаце найденного документа.