Сводные таблицы

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

Создание макросов

В Excel часто выполняемые задачи можно автоматизировать путем создания макросов. Макрос — это набор команд и инструкций, группируемых вместе в виде единой команды для автоматического выполнения задачи. Как правило, макросы используются для: ускорения часто выполняемых операций редактирования или форматирования; объединения нескольких команд, например вставки таблицы с определенными размерами, границами и числом строк и столбцов; упрощения доступа к параметрам в диалоговых окнах; автоматизации обработки сложных последовательных действий в задачах. Макросы запускаются следующим образом: Вид Макросы Запись макроса(Рис 1), затем переходим на нужный нам лист и останавливаем запись. Присваиваем ему имя и делаем для него кнопку или значок(Рис 2). Тем же способом записываем остальные макросы.

Рис 1. Запись Макроса Рис 2. Выбор вида Макроса

3. Проектная часть

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

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

Из предложенного документа «Таблица комплектующих» (ПРИЛОЖЕНИЕ 2) 

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

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

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

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

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

(ПРИЛОЖЕНИЕ 3)

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

Получаем нужную таблицу для дальнейшей работы (ПРИЛОЖЕНИЕ 4).

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

Цена продаж:

В соответствии с данным условием в задании получаем формулу: =ЕСЛИ(И(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 вводим формулу:

=ИНДЕКС('Вариант 9'!$B$2:$B$11;ПОИСКПОЗ('Расчёт устройства '!B2;'Вариант 9'!$A$2:$A$11;0))

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

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

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

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

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

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

Необходим для создания сводной таблицы. Для этого в ячейке L2 вводим формулу:

=ОКРУГЛВНИЗ(F2/12;0)