Расчёт устройства микроэлектроники с помощью Microsoft Excel

Функция ПОИСКПОЗ выполняет поиск указанного элемента в диапазоне ячеек и возвращает относительную позицию этого элемента в диапазоне. 

Пример: ПОИСКПОЗ(искомое_значение, просматриваемый_массив, [тип_сопоставления])                                                                                                  Аргументы функции ПОИСКПОЗ:               

1)Искомое_значение.    Обязательный аргумент. Значение, которое сопоставляется со значениями в аргументе просматриваемый_массив. Например, при поиске номера в телефонной книге имя абонента указывается в качестве искомого значения, а нужным значением будет номер телефона. Аргумент искомое_значение может быть значением (числом, текстом или логическим значением) или ссылкой на ячейку, содержащую такое значение. 2)Просматриваемый_массив.    Обязательный аргумент. Диапазон ячеек, в которых производится поиск.

3)Тип_сопоставления.    Необязательный аргумент. Число -1, 0 или 1. Аргумент тип_сопоставления указывает, каким образом в Microsoft Excel искомое_значение сопоставляется со значениями в аргументе просматриваемый_массив. 

Построение  диаграмм 

     В программе Excel термин диаграмма используется для обозначения всех видов графического представления числовых данных. Основными типами диаграмм являются: график, гистограмма, круговая диаграмма. Построение графического изображения производится на основе ряда данных. Так называют диапазон ячеек с числовыми значениями в пределах отдельной строки или столбца. На одной диаграмме можно отображать несколько рядов данных.                                                                                                                          К построенной диаграмме автоматически добавляется легенда – расшифровка рядов данных. В нашей курсовой работе, по заданию, нам потребуется построить график показывающий цену изготовления комплектующей и заводы – изготовители. Строить диаграммы и графики в Excel не составляет особого труда, главное ввести данные для диаграммы. Пример нашей диаграммы(ПРИЛОЖЕНИЕ 7) 

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

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

 
            3.2  Выполнение индивидуального задания

      Рассчитать устройство, в котором  цена продаж каждой комплектующей ниже средней (для данной комплектующей) и срок его годности. Построить  график, показывающий цену изготовления комплектующей и заводы – изготовители (для одной произвольной комплектующей).С начало с помощью сводной таблицы найдём комплектующие цена которых ниже средней. Создаём сводную таблицу: Вставка         Сводная таблица.

     В списке полей сводной таблицы  указываем необходимые нам, это  Обозначение(переименовываем как Комплектующие);Цена продаж. Чтобы наше условие выполнялось необходимо перенести Цена продаж в область Значение, и указать через параметры полей значения операцию Минимум.

     Чтобы вычислить срок годности необходимо создать столбец Срок годности. Каждый раз формулу необходимо писать заново через кнопку Вставить функцию. В дальнейшем нам поможет Мастер функций. В Шаге 1 выбираем Аргументы (массив: номер строки; номер столбца), переходим к функции Индекс, в Массиве выбираем нужный нам лист – «Расчёт устройства», и необходимую деталь. В Номер строки используем функцию ПОИСКПОЗ и получаем данные со «Сводной таблице» по нашей комплектующей. Выбираем Цену продаж и Обозначение. Далее переходим на Цену продаж на листе «Расчёт устройства» и выбираем необходимую нам. Для детали Д226Б формула выглядит так:

=ИНДЕКС('Расчёт устройства'!I2:I3;ПОИСКПОЗ(ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Цена продаж";$A$1;"Обозначение";"Д 226Б");'Расчёт устройства'!H2:H3;0))

В конечном счёте у нас получается следующее(ПРИЛОЖЕНИЕ 5)

С помощью  расширенного фильтра создаём таблицу, и построим с её помощью диаграмму, в которой будет наглядно видно цена продажи для отдельно взятой детали на разных заводах изготовителей. В сводную таблицу включаем следующие поля Обозначение, Изготовитель, Цена изготовления. Выбираем деталь, нашем случае это КТ312Б,получаем(ПРИЛОЖЕНИЕ 6)

Через вставку выбираем  диаграмму и  вставляемеё на новый лист. Должно получится следующее (ПРИЛОЖЕНИЕ 7).

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

ЗАКЛЮЧЕНИЕ 

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

СПИСОК  ЛИТЕРАТУРЫ

1. Сайт http://ru.wikipedia.org
2. Сайт http://office.microsoft.com

 

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

ПРИЛОЖЕНИЯ

ПРИЛОЖЕНИЕ 2  «Основная таблица»

Наименование	Обозначение	Цена  Изготовления, руб	Дата  Изготовления	Изготовитель	Срок  хранения, мес
Переключатель 3х позиционный	КЭМ-2	9,8	12.01.99	Рижский приборостроительный  завод	176
Конденсатор	К53-1 500 мкФ*25 В	22,6	28.01.99	Рижский приборостроительный  завод	104
Усилитель	К144УД1	9,8	27.04.99	"Красный  луч" г. Новая Каховка	150
Конденсатор	КМ - 6 470Ф	21,8	16.02.00	"ТрансПрибор"  г. Санкт-Петербург	90
Стабилитрон	КС515А	2,2	04.03.00	"Электронприбор" г. Тольятти	156
Диод	Д223	13,8	23.03.00	Загорский ферритовый завод	112
Резистор	МЛТ- 0,125  390 кОм	5,0	09.04.00	"ТрансПрибор"  г. Санкт-Петербург	153
Светодиод	АЛ202 А	2,7	03.05.00	Львовский приборный  завод	122
Транзистор	КТ312Б	10,5	14.05.00	Львовский приборный  завод	162
Резистор	МЛТ- 0,5  150 Ом	4,8	22.05.00	"Красный  луч" г. Новая Каховка	144
Конденсатор подстроечный	КТ4-27 15 мкФ	29,6	02.06.00	Рязанский радиозавод	172
Резистор	МЛТ- 0,5  5,6 кОм	5,7	07.06.00	Львовский приборный  завод	135
Конденсатор	К53-1 500 мкФ*25 В	21,3	18.06.00	Львовский приборный  завод	80
Транзистор	КТ603Б	10,2	26.06.00	Минский завод  электронных приборов	138
Резистор	МЛТ- 0,5  5,6 кОм	5,3	07.07.00	Рижский приборостроительный  завод	160
Конденсатор подстроечный	КТ4-27 15 мкФ	31,0	13.07.00	Загорский ферритовый завод	170
Светодиод	АЛ202 А	2,4	26.07.00	Минский завод  электронных приборов	120
Диод	Д 814А	12,7	31.07.00	"Морфиз" г.  Санкт-Петербург	144
Тиристор	КУ101Г	2,5	11.08.00	Рязанский радиозавод	140
Резистор  подстроечный	СП 5-2  32 Ом	8,1	17.08.00	Рязанский радиозавод	132
Конденсатор	КМ-4 0,15 мкФ	24,9	05.09.00	"ТрансПрибор"  г. Санкт-Петербург	90
Транзистор	КТ209	10,0	21.09.00	Львовский приборный  завод	136
Усилитель	К544УД2А	9,9	10.10.00	"Морфиз" г.  Санкт-Петербург	150
Стабилитрон	КС515А	2,1	27.10.00	Загорский ферритовый завод	140
Резистор	МЛТ- 0,5  150 Ом	5,2	14.11.00	"ТрансПрибор"  г. Санкт-Петербург	153
Светодиод	АЛ202 А	2,9	01.12.00	Загорский ферритовый завод	120
Диод	Д223	12,9	20.12.00	"Морфиз" г.  Санкт-Петербург	150
Переключатель 3х позиционный	КЭМ-2	11,1	05.01.01	Загорский ферритовый завод	160
Тиристор	КУ101Г	2,6	24.01.01	Минский завод  электронных приборов	132
Транзистор	КТ 315В	10,5	04.04.01	Минский завод  электронных приборов	138
Стабилитрон	КС515А	2,5	23.04.01	"Красный  луч" г. Новая Каховка	143
Конденсатор	КТ-2 0,33 мкФ	25,5	10.05.01	"ТрансПрибор"  г. Санкт-Петербург	90
Тиристор	КУ101Г	2,4	28.05.01	"ТрансПрибор"  г. Санкт-Петербург	142
Резистор  подстроечный	СП 5-2  32 Ом	8,2	14.06.01	Минский завод  электронных приборов	114
Усилитель	К174ПС1	10,0	03.07.01	"Красный  луч" г. Новая Каховка	150
Усилитель	К174ПС1	10,2	19.07.01	"Электронприбор" г. Тольятти	148
Переключатель 3х позиционный	КЭМ-2	10,6	07.08.01	"Красный  луч" г. Новая Каховка	170
Светодиод	АЛ307Б	2,6	24.08.01	Минский завод  электронных приборов	122
Конденсатор подстроечный	КТ4-27 15 мкФ	30,0	11.09.01	Львовский приборный  завод	168
Резистор	МЛТ- 0,125  390 кОм	4,8	28.09.01	Загорский ферритовый завод	130
Резистор  подстроечный	СП 5-2  32 Ом	9,0	17.10.01	"ТрансПрибор"  г. Санкт-Петербург	120
Светодиод	АЛ202 А	2,8	02.11.01	"Красный  луч" г. Новая Каховка	124
Конденсатор	К53-1 500 мкФ*25 В	21,0	21.11.01	"Морфиз" г.  Санкт-Петербург	110
Транзистор	КТ 315В	11,6	10.12.01	"Морфиз" г.  Санкт-Петербург	158
Конденсатор	КТ-2 0,33 мкФ	24,9	26.12.01	Львовский приборный  завод	80
Диод	Д 226Б	12,4	14.01.02	"Красный  луч" г. Новая Каховка	144
Резистор	МЛТ- 0,5  150 Ом	5,1	27.01.02	Рижский приборостроительный  завод	160
Диод	КД208А	12,9	30.01.02	Загорский ферритовый завод	112
Диод	Д 814А	13,6	03.02.02	"Электронприбор" г. Тольятти	142
Транзистор	КТ209	9,9	18.02.02	"Красный  луч" г. Новая Каховка	154
Транзистор	КТ209	12,0	26.02.02	"Морфиз" г.  Санкт-Петербург	158
Тиристор	КУ101Г	2,4	03.03.02	Львовский приборный  завод	130
Транзистор	КТ 315В	11,8	05.03.02	"Красный  луч" г. Новая Каховка	154
Конденсатор	КМ-4 0,15 мкФ	22,9	07.03.02	Рижский приборостроительный  завод	104
Транзистор	КТ 315В	10,9	16.03.02	Львовский приборный  завод	136
Диод	КД208А	14,0	25.03.02	Львовский приборный  завод	104
Конденсатор	К53-1 500 мкФ*25 В	20,7	25.03.02	Загорский ферритовый завод	85
Транзистор	КТ312Б	11,0	25.03.02	Минский завод  электронных приборов	138
Резистор	МЛТ- 0,5  5,6 кОм	4,9	11.04.02	"ТрансПрибор"  г. Санкт-Петербург	153
Усилитель	К144УД1	10,2	11.04.02	"Электронприбор" г. Тольятти	148
Транзистор	КТ603Б	11,4	20.04.02	"Электронприбор" г. Тольятти	145
Светодиод	АЛ307Б	2,8	25.04.02	"Электронприбор" г. Тольятти	124
Стабилитрон	КС515А	2,6	28.04.02	Минский завод  электронных приборов	146
Транзистор	КТ209	10,7	30.04.02	"Электронприбор" г. Тольятти	145
Транзистор	КТ312Б	11,2	30.04.02	Рязанский радиозавод	148
Транзистор	КТ603Б	10,9	30.04.02	Рязанский радиозавод	148
Усилитель	К544УД2А	10,5	30.04.02	Минский завод  электронных приборов	140
Конденсатор	КМ - 6 470Ф	21,8	10.05.02	Рижский приборостроительный  завод	104
Переключатель 3х позиционный	КЭМ-2	11,0	16.05.02	Рязанский радиозавод	172
Резистор	МЛТ- 0,5  20 кОм	5,6	16.05.02	"ТрансПрибор"  г. Санкт-Петербург	153
Транзистор	КТ312Б	10,8	16.05.02	"Электронприбор" г. Тольятти	145
Переключатель 3х позиционный	КЭМ-2	10,0	14.06.02	"ТрансПрибор"  г. Санкт-Петербург	164
Резистор  подстроечный	СП 5-2  32 Ом	8,5	30.06.02	Львовский приборный  завод	117
Конденсатор	КМ - 6 470Ф	24,2	06.07.02	Загорский ферритовый завод	85
Диод	КД208А	14,1	16.07.02	"Электронприбор" г. Тольятти	142
Конденсатор	КТ-2 0,33 мкФ	25,3	17.07.02	Рижский приборостроительный  завод	104
Стабилитрон	КС515А	2,5	22.07.02	Львовский приборный  завод	144
Стабилитрон	КС515А	2,2	28.07.02	"ТрансПрибор"  г. Санкт-Петербург	148
Конденсатор подстроечный	КТ4-27 15 мкФ	30,6	02.08.02	"ТрансПрибор"  г. Санкт-Петербург	178
Конденсатор	КМ - 6 470Ф	23,6	11.08.02	"Морфиз" г.  Санкт-Петербург	110
Конденсатор	КМ-4 0,15 мкФ	25,0	19.08.02	"Красный  луч" г. Новая Каховка	98
Стабилитрон	КС515А	2,4	27.08.02	"Морфиз" г.  Санкт-Петербург	150
Транзистор	КТ312Б	9,6	09.09.02	"Морфиз" г.  Санкт-Петербург	158
Тиристор	КУ101Г	2,8	25.09.02	Загорский ферритовый завод	130
Конденсатор подстроечный	КТ4-27 15 мкФ	29,8	30.09.02	Рижский приборостроительный  завод	173
Резистор	МЛТ- 0,5  20 кОм	5,3	30.09.02	Рижский приборостроительный  завод	160
Транзистор	КТ209	9,9	10.10.02	Рязанский радиозавод	148
Конденсатор	КТ-2 0,33 мкФ	26,0	12.10.02	"Морфиз" г.  Санкт-Петербург	110
Тиристор	КУ101Г	2,5	04.11.02	"Красный  луч" г. Новая Каховка	144
Светодиод	АЛ202 А	2,9	07.12.02	Рязанский радиозавод	122
Резистор	МЛТ- 0,125  390 кОм	4,2	16.12.02	"Красный  луч" г. Новая Каховка	144
Резистор	МЛТ- 0,5  150 Ом	5,4	28.12.02	Загорский ферритовый завод	130
Транзистор	КТ 315В	9,8	30.08.03	Рязанский радиозавод	148
Резистор	МЛТ- 0,5  20 кОм	5,8	16.09.03	Львовский приборный  завод	135
Резистор  подстроечный	СП 5-2  32 Ом	9,0	04.10.03	Рижский приборостроительный  завод	124
Усилитель	К544УД2А	9,6	21.10.03	"Красный  луч" г. Новая Каховка	150
Транзистор	КТ603Б	11,0	09.11.03	"Морфиз" г.  Санкт-Петербург	158
Конденсатор	КМ-4 0,15 мкФ	24,8	25.11.03	"Морфиз" г.  Санкт-Петербург	110
Усилитель	К144УД1	9,6	18.01.04	Минский завод  электронных приборов	140
Резистор	МЛТ- 0,5  20 кОм	5,0	04.02.04	"Красный  луч" г. Новая Каховка	144
Стабилитрон	КС515А	2,6	22.02.04	Рязанский радиозавод	159
Конденсатор	КТ-2 0,33 мкФ	24,9	10.03.04	Рязанский радиозавод	94
Транзистор	КТ 315В	10,4	29.03.04	"Электронприбор" г. Тольятти	145
Тиристор	КУ101Г	2,6	14.12.04	"Электронприбор" г. Тольятти	144
Транзистор	КТ209	9,6	31.12.04	Минский завод  электронных приборов	138
Светодиод	АЛ307Б	2,7	09.02.05	Загорский ферритовый завод	120
Транзистор	КТ312Б	9,5	28.02.05	"Красный  луч" г. Новая Каховка	154
Светодиод	АЛ307Б	2,5	19.03.05	Рязанский радиозавод	124
Усилитель	К544УД2А	10,8	25.03.05	"Электронприбор" г. Тольятти	148
Усилитель	К544УД2А	10,4	25.03.05	"Морфиз" г.  Санкт-Петербург	150
Диод	Д 226Б	14,2	11.04.05	"Электронприбор" г. Тольятти	142
Диод	Д 814А	15,0	11.04.05	"Красный  луч" г. Новая Каховка	144
Диод	Д223	16,9	11.04.05	Львовский приборный  завод	104
Усилитель	К174ПС1	10,0	14.04.05	Минский завод  электронных приборов	140
Конденсатор	К53-1 500 мкФ*25 В	22,5	01.04.06	"ТрансПрибор"  г. Санкт-Петербург	90
Конденсатор	К53-1 500 мкФ*25 В	23,0	01.04.06	"Красный  луч" г. Новая Каховка	98
Конденсатор	КМ - 6 470Ф	27,8	06.04.06	"Красный  луч" г. Новая Каховка	98
Резистор	МЛТ- 0,125  390 кОм	4,6	11.04.06	Рижский приборостроительный  завод	160
Диод	Д223	14,5	21.04.06	"Красный  луч" г. Новая Каховка	144
Конденсатор	КМ - 6 470Ф	23,9	19.06.06	Львовский приборный  завод	80
Конденсатор	КМ-4 0,15 мкФ	21,6	25.11.06	Загорский ферритовый завод	85