ОКРУГЛВНИЗ. Округляет число до ближайшего меньшего по модулю значения.

Возвращает значение или ссылку на значение из таблицы или интервала. Функция ИНДЕКС имеет две синтаксические формы: ссылка и массив. Ссылочная форма всегда возвращает ссылку; форма массива всегда возвращает значение или массив значений.

ИНДЕКС (массив; номер_строки; номер_столбца) возвращает значение указанной ячейки или массив значений в аргументе массив.

ИНДЕКС (ссылка; номер_строки; номер_столбца; номер_области) возвращает ссылку на указанную ячейку или ячейки в аргументе ссылка.

ПОИСКПОЗ

Возвращает относительное положение элемента массива, который соответствует заданному значению указанным образом. Функция ПОИСКПОЗ используется вместо функций типа ПРОСМОТР, если нужна позиция элемента в диапазоне, а не сам элемент.

ПОИСКПОЗ (Искомое_значение, п росматриваемый_массив, тип_сопоставления)

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

Просматриваемый_массив - это непрерывный интервал ячеек, возможно, содержащих искомые значения. Просматриваемый_массив может быть массивом или ссылкой на массив.
Тип_сопоставления - это число - 1, 0 или 1. Тип_сопоставления указывает, как Microsoft Excel сопоставляет искомое_значение со значениями в аргументе просматриваемый_массив.

Если тип_сопоставления равен 1, то функция ПОИСКПОЗ находит наибольшее значение, которое равно или меньше, чем искомое_значение. Просматриваемый_массив должен быть упорядочен по возрастанию:..., - 2, - 1, 0, 1, 2,..., A-Z, ЛОЖЬ, ИСТИНА. Если тип_сопоставления равен 0, то функция ПОИСКПОЗ находит первое значение, которое в точности равно аргументу искомое_значение. Просматриваемый_массив может быть в любом порядке.
Если тип_сопоставления равен - 1, то функция ПОИСКПОЗ находит наименьшее значение, которое равно и больше чем искомое_значение. Просматриваемый_массив должен быть упорядочен по убыванию: ИСТИНА, ЛОЖЬ, Z-A,..., 2, 1, 0, - 1, - 2,..., и так далее.
Если тип_сопоставления опущен, то предполагается, что он равен 1.

3.ПРОЕКТНАЯ ЧАСТЬ

3.1 СОЗДАНИЕ ОСНОВНОЙ ТАБЛИЦЫ

Создаём новый документ MS Excel

Из предложенного документа Таблица комплектующих ( Приложение 1 )

копируем данную таблицу в Новую книгу MS Excel. Из задания копируем список предложенных деталей своего варианта и вставляем её в документ MS EXCEL.

Фильтруем таблицу комплектующих с помощью Расширенного фильтра (Данные \ Фильтр \ Расширенный фильтр):

В окне Расширенный фильтр выбираем:

Исходный диапазон: вся таблица вместе с заголовками.

Диапазон условий: скопированный список деталей из задания

(Приложение2)

Фильтруем список на новый лист «Расчёт устройства комплектующих»

Получаем нужную таблицу для дальнейшей работы (Приложение 3).

Добавляем в нашу таблицу следующие столбцы:

Цена продаж:

В соответствии с данным условием в задании получаем формулу:

=ЕСЛИ(И(C2<10;(I2-СЕГОДНЯ())/365,25>=3); C2*1,4;ЕСЛИ(И(C2>=10;C2<20);C2*1,3;ЕСЛИ(И(C2>20;(I2-СЕГОДНЯ())/365,25>=2);C2*1,2;C2))) в которой используем функции ЕСЛИ, И,УМНОЖЕНИЕ.

Доход:
Получаем путём умножения Цены продаж и Количества деталей.
Для этого в ячейке J2 вводим формулу: =H2*G2

Чистая прибыль:

Получаем как разность Дохода и произведения Цены изготовления на Количество деталей.

Для этого в ячейке К2 вводим формулу: =J2-C2*G2

Срок годности:

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

Для этого в ячейке I2 вводим формулу:=D2+F2*30.

То есть количество месяцев умножаем на количество дней в месяце, в среднем. Получаем срок хранения в днях и прибавляем эти дни к Дате изготовления. При этом в ячейке I2 устанавливаем формат ячейки - Дата. ( Формат \ Ячейки \ Числовой формат - Дата).

Количество деталей:

Автоматически переводим из варианта задания. Для этого в ячейке G2 вводим формулу:

=ИНДЕКС('Вариант 15'!$B$2:$B$11;ПОИСКПОЗ('Расчёт устройства комплектующих'!B2;'Вариант 15'!$A$2:$A$11;0))

В данной формуле:

'Вариант 15'!$B$2:$B$11 - ссылка на массив данных,в котором находится необходимое количество деталей заданных по условию.

ПОИСКПОЗ('Расчёт устройства комплектующих'!B2;'Вариант 15'!$A$2:$A$11;0) - выдаёт № строки в искомом массиве, в котором находится искомое значение.

'Расчёт устройства комплектующих'!B2 - абсолютная ссылка на искомое значение - Обозначение детали.

'Вариант 15'!$A$2:$A$11 - абсолютная ссылка на просматриваемый массив.
0 - тип сопоставления (то есть значение равно искомому).

Срок годности в годах:

Необходим для создания сводной таблицы.Для этого в ячейке L2 вводим формулу: =ОКРУГЛВНИЗ(F2/12;0)

Примечание.

Все приведённые выше формулы для второй строки аналогичным способом применяем для следующих строк до 48-ой.

3.2 ВЫПОЛНЕНИЕ ИНДИВИДУАЛЬНОГО ЗАДАНИЯ

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

Для начала с помощью расширенного фильтра создаём таблицу комплектующих срок годности которых более 7 лет.(Приложение 4)

ДАННЫЕ → ФИЛЬТР → РАСШИРЕННЫЙ ФИЛЬТР

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

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

ДАННЫЕ → СВОДНАЯ ТАБЛИЦА

Затем необходимо сосчитать стоимость всего устройства. Для этого вносим в таблицу 2 графы: Количество деталей и Итого (Приложение 6). В графе количество указываем количество комплектующих,а в графе Итого в ячейке I88 вводим формулу:

=G88*H88

В конце таблицы делаем общий итог, чтоб сосчитать общую стоимость устройства. Для этого в ячейке I102 вводим формулу:

=СУММ(I88;I89;I91;I92;I94;I96;I98;I99;I101)

Для построения диаграммы используем мастер диаграмм

ВСТАВКА → ДИАГРАММА

Затем выбираем какой вид диаграммы нам больше нравится и нажимаем ОК. Чтобы на диаграмме что либо отобразилось необходимо задать ей данные. Нажимаем на панели инструментов «Выбрать Данные» и в качестве данных используем сводную таблицу (Приложение 5). После появления диаграммы переносим её на отдельный лист, для этого в «Конструкторе диаграмм» на панели инструментов вы бираем «Переместить Диаграмму» и выбираем «На отдельном листе». Даём ей название. (Приложение 7)

ЗАКЛЮЧЕНИЕ

Программы Microsoft Word и Excel являются самыми распространённым в работе с документами и таблицами. Для этих программ предусмотрен широкий выбор средств автоматизации, упрощающих выполнение типичных задач, что чрезвычайно удобно. Именно поэтому данное семейство Office корпорации Microsoft используется на многих предприятиях и в частных фирмах.