Институт агроинженерии ФГБОУ ВО Южно-Уральский ГАУ

Функция ИНДЕКС

Синтаксис функции:

ИНДЕКС(массив;номер_строки;номер_столбца)

1. Аргумент «массив» - диапазон ячеек или массив констант. Если массив содержит только одну строку или один столбец, аргумент «номер_строки» (или, соответственно, «номер_столбца») не является обязательным.

2. Номер_строки - номер строки в массиве, из которой требуется возвратить значение. Если аргумент «номер_строки» опущен, аргумент «номер_столбца» является обязательным.

3. Номер_столбца - номер столбца в массиве, из которого требуется возвратить значение. Если аргумент «номер_столбца» опущен, аргумент «номер_строки» является обязательным.

Замечания

1. Если используются оба аргумента — и «номер_строки», и «номер_столбца», — то функция ИНДЕКС возвращает значение, находящееся в ячейке на пересечении указанных строки и столбца.

2. Значения аргументов «номер_строки» и «номер_столбца» должны указывать на ячейку внутри заданного массива; в противном случае функция ИНДЕКС возвращает значение ошибки #ССЫЛ!.

Пример 4. Закончим предыдущую задачу о выборе фаски по делительному диаметру. Определение фаски

В результате расчета получили число 3,т. е. значение, которое стоит на 6 месте в диапазоне Фаска.Измените фон ячейки с полученным результатом, чтобы выделить ответ.

Задание 1. На листе Справочники создайте два именованных диапазона Режим и Коэффициент:

На листе 1 создайте диапазон по рисунку:

1. Выполните определение номера позиции в диапазоне Коэффициент по заданному значению, например, 0,7 с помощью функции ПОИСПОЗ:

2. Найдите режим нагружения с помощью функции ИНДЕКС, выделите ответ

Задание 2. Дана таблица определения припуска по габаритному диаметру (ниже). Выполните определение припуска по известному значению габаритного диаметра и рассчитайте диаметр заготовки по формуле : Dz=Dgab+Prip

Диаметр габаритный	припуск
(0, 18]	3,2
(18, 30]	3,6
(30, 50]	4,6
(50, 80]	5,2
(80, 120]	5,8
(120, 180]	6,8
(180, 250]	7,4
(250, 320]	8,2
(320, 400]	8,8
(400, 500]	9,8
(500, 630]	10,6
(630, 800]	12,4
(800, 1000]	14,0
(1000, 1250]	15,8
(1250, 1600]	18,2
(1600, 2000]	21,0
(2000, 2500]	24,4
(2500, 3150]	28,8
(3150, )	36,0

Рекомендации к решению. В Excel заполните диапазон исходных данных на листе Справочники и ячейки с результатом на листе 1 по рисунку. Дайте имена диапазонам Диаметр и Припуск. Выделите ячейку с результатом расчета

Обратите внимание, что на рисунке расположение значений диаметров – от максимального к минимальному (почему?). Учтите этот факт в использовании функции ПОИСКПОЗ.

Расчет освещения в Excel

1. Откройте Excel

2. Переименуйте лист 1 –Расчет, лист 2 – Коэффициенты, лист 3 – Светильники, лист 4 - Таблицы

3. На листе Расчет наберите в столбце А:

исходные данные
Марка светильника
коэффициент отражения
длина помещения (м)
ширина помещения (м)
высота помещения (м)
наименьшая освещенность E (лк)
коэффициент запаса k
коэффициент минимальной освещенности z, равный отношению средней освещенности к минимальной
Расчетное значение i индекса помещения
индекс помещения I (табл)
коэффициент использования светового потока освет. установки, h
Световой поток
число светильников (шт)
Вспомогательные расчеты
номер строки в к-ах использования
номер строки в к-ах отражения
тип светильника для таблицы к-ов. использования
столбец в таблицах коэффициентов для заданного типа светильника

4. На листе Коэффициенты создайте два диапазона

Коэффициенты отражения	Индексы помещения
80/80/30
80/50/30
80/30/10
70/50/20	2,5
50/50/10
50/30/10	1,5
30/30/10	1,25
	0,8
	0,6

5. На листе Светильники поместить таблицу с данными о светильниках (по заданному варианту, см. стр. 23), например:

Марка светильника	К-во ламп	Мощность лампы/тип цоколя	Световой поток лампы, лм	Таблица к-тов использования для светильника
PRS/R 418 «С.Т.»		18Вт/G13/люм.		PRS 418, 436
OWP/R 418 «С.Т.»		18Вт/G13/люм.		OWP 336; OWP/R 418
ЛПП-07В-2х36 «Ватра»		36Вт/G13/люм.		ЛПП
ЛПП-07В-1х36 «Ватра»		36Вт/G13/люм.		ЛПП
ЛПП-07В-2х58 «Ватра»		58Вт/G13/люм.		ЛПП
ЛПП-07В-2х36 «Ватра»		36Вт/G13/люм.		ЛПП
НББ-64В-60 «Селена»		60Вт/E27/нак.		НББ
НСП11-100 «Ватра»		100Вт/E27/нак.		НСП

6. На листе Таблицы поместить таблицу с данными о коэффициентах использования (по Вашему варианту, см. стр. 20-23), например

Перейдите к листу Расчет.

1. Выделите ячейку B2.

2. На вкладке Данные вгруппе«Работа с данными»щелкнуть кнопку«Проверка данных» .

3. Используя окно проверки, выполнить настройку следующим образом:

На вкладке Параметры указать:

Тип данных - Список

Источник - =Марка_светильника

4. Нажмите ОК.

5. Возле ячейки B2 должна появиться стрелка раскрывающегося списка. Выберите из списка светильник PRS/R 418 «С.Т.»

6. Аналогично оформите для ячейки B3 выбор коэффициента отражения. Установите из списка значение 70/50/20

7. Выделите ячейку B7.

8. Используя окно проверки, выполнить настройку следующим образом:

На вкладке Параметры указать:

Тип данных - Список

Источник - 10;15;20;30;50;75;100;150;200;300;400;500

9. Возле ячейки B7 должна появиться стрелка раскрывающегося списка. Выберите из списка значение 75.

10. Аналогично п.8 в ячейке B8 установите выбор коэффициента запаса из двух значений: 1,2; 1,4 и в ячейке B9 коэффициента Z: 1,1; 1,15

В ячейки B4, B5 и B6 введите длину - 11,7, высоту - 3 и ширину помещения 2,3 соответственно. Выполните расчет индекса помещения по формуле (2) в ячейке B10 (должно получиться значение 0,6407).

Порядок заполнения

1.Выделить столбец B.
В контекстном меню выбрать команду «Вставить» (не путайте с командой «Вставить» из буфера обмена).
В результате будет добавлен новый столбец, с форматом первого столбца.

2.Теперь следует щелкнуть стрелку кнопки «Параметры добавления» и выбрать вариант «Форматировать как справа»

3. Заполнить согласно варианту новыми данными ячейки В2:В9. Формулы из ячеек С10:С14 и С16:С19 скопировать в столбец В.

4. Повторить п. 1-3 для заданного количества помещений.

Создайте новый рабочий лист «Сводная»

1. Создайте на этом рабочем листе «шапку» таблицы:

2. На листе «Расчет» выделите ячейки с названиями помещений и скопируйте их в буфер обмена.

3. Перейдите к листу «Сводная». Выделите ячейку А2, на вкладке «Главная» разверните кнопку «Вставить» и выберите команду «Транспонировать»:

4. На листе «Расчет» выделите ячейки с B2:F2 и скопируйте их.

5. Перейдите к листу «Сводная». Выделите ячейку В2, на вкладке «Главная» разверните кнопку «Вставить», выберите команду «Специальная вставка» и заполните окно по рисунку:

6. В столбце «Кол-во ламп» округлить полученное значение N до ближайшего целого с помощью функции ОКРУГЛВВЕРХ()

7. Выделите ячейку В1, на вкладке «Вставка» выберите команду «Сводная таблица». В диалоговое окно должно иметь вид:

8. Нажмите ОК. Заполните список полей сводной таблицы по рисунку:

Необходимые поля просто перетаскивайте мышью в нужную область: «Марка светильника» в «Названия строк», «Кол-во ламп» в «Значения». Сводная таблица появится на макете.

Исходные данные для расчетов. Выбрать любые 10 помещений.

№ помещ.	Помещение	Марка светильника	Kз	Z	длина, м	ширина, м	Высота. м
	Кабинет №1		1,4	1,1	5,7	2,9	2,2
	Канцелярия		1,4	1,1	6,6	2,8	2,2
	Лаборатория №1		1,4	1,1	2,9	2,8	3,0
	Лаборатория №2		1,4	1,1	3,6	2,8	2,2
	Рабочий зал		1,4	1,1	6,4	4,4	2,2
	Кабинет №2		1,4	1,1	3,4	2,8	2,2
	Лаборантская		1,4	1,1	5,6	2,7	3,0
	Комната отдыха		1,4	1,1	5,7	3,3	2,2
	Приемная		1,4	1,1	3,7	3,3	2,2
	Кабинет директора		1,4	1,1	6,0	3,7	2,2
	Кладовая		1,4	1,1	3,7	3,3	2,2
	Лестничная клетка		1,4	1,1	5,6	2,5	3,0
	Душевая №1		1,2	1,15	2,5	0,9	3,0
	Душевая №2		1,4	1,1	2,6	1,5	3,0
	Теплогенераторная		1,4	1,1	6,0	2,2	2,2
	Тамбур		1,2	1,15	2,0	2,0	3,0
	Холл		1,4	1,1	5,7	3,5	3,0
	Столовая		1,4	1,1	8,2	2,7	3,0
	Комната охраны		1,4	1,1	2,8	2,2	2,2
	Архив		1,4	1,1	4,1	2,2	2,2
	Бухгалтерия		1,4	1,1	6,0	3,3	2,2
	Лаборатория №3		1,4	1,1	5,7	3,5	2,2
	Аудитория №1		1,4	1,1	5,7	2,3	2,2
	Аудитория №2		1,4	1,1	4,3	4,1	2,2
	Аудитория №3		1,4	1,1	5,9	3,3	2,2
	Коридор		1,4	1,1	14,8	1,8	3,0
	Комната для инвентаря		1,4	1,1	3,7	2,0	3,0
	Кабинет главного инженера		1,4	1,1	3,8	3,7	2,2
	Служебный кабинет		1,4	1,1	3,7	3,0	2,2
	Комната мастеров		1,4	1,1	3,7	3,3	2,2
	Серверная		1,4	1,1	3,7	2,2	2,2
	Электротехническая мастерская		1,4	1,1	27,0	5,7	2,2
	Тамбур		1,4	1,1	5,6	2,5	3,0
	Терраса		1,2	1,15	27,0	12,6	6,0

№ варианта (последние цифры шифра)	Таблица коэффициентов использования
	LB 250S, 250H tulubar
	LBF
	OPL 218,236,258
	OPL 418, 436
	OWP 255
	OWP 336; OWP/R 418
	PRB 218, 336, 258
	PRB 418, 436
	PRS 418, 436
	WAVE LED 595
Все варианты	ЛПП

ТАБЛИЦЫ КОЭФФИЦИЕНТОВ ИСПОЛЬЗОВАНИЯ

LB 250S, 250H tulubar	80/80/30	80/50/30	80/30/10	70/50/20	50/50/10	50/30/10	30/30/10
0,6
0,8
1,25
1,5
2,5

LBF	80/80/30	80/50/30	80/30/10	70/50/20	50/50/10	50/30/10	30/30/10
0,6
0,8
1,25
1,5
2,5

OPL 218,236,258	80/80/30	80/50/30	80/30/10	70/50/20	50/50/10	50/30/10	30/30/10
0,6
0,8
1,25
1,5
2,5

OPL 418, 436	80/80/30	80/50/30	80/30/10	70/50/20	50/50/10	50/30/10	30/30/10
0,6
0,8
1,25
1,5
2,5

OWP 255	80/80/30	80/50/30	80/30/10	70/50/20	50/50/10	50/30/10	30/30/10
0,6
0,8
1,25
1,5
2,5

OWP 336; OWP/R 418	80/80/30	80/50/30	80/30/10	70/50/20	50/50/10	50/30/10	30/30/10
0,6
0,8
1,25
1,5
2,5

PRB 218, 336, 258	80/80/30	80/50/30	80/30/10	70/50/20	50/50/10	50/30/10	30/30/10
0,6
0,8
1,25
1,5
2,5

PRB 418, 436	80/80/30	80/50/30	80/30/10	70/50/20	50/50/10	50/30/10	30/30/10
0,6
0,8
1,25
1,5
2,5

PRS 418, 436	80/80/30	80/50/30	80/30/10	70/50/20	50/50/10	50/30/10	30/30/10
0,6
0,8
1,25
1,5
2,5

WAVE LED 595	80/80/30	80/50/30	80/30/10	70/50/20	50/50/10	50/30/10	30/30/10
0,6
0,8
1,25
1,5
2,5

ЛПП	80/80/30	80/50/30	80/30/10	70/50/20	50/50/10	50/30/10	30/30/10
0,6
0,8
1,25
1,5
2,5

СВЕТИЛЬНИКИ (выбрать по таблицам коэффициентов использования)

Марка светильника	К-во ламп	Мощность лампы/тип цоколя	Световой поток лампы, лм	Таблица к-тов использования
LB 150 HR (МГЛ)		150Вт/RX7s/МГЛ		LB 250S, 250H tulubar
LB 250 (МГЛ)		250Вт/E40/МГЛ		LB 250S, 250H tulubar
LBF 250M (ДРЛ)		250Вт/Е40/ДРЛ		LBF
LBF 250 (МГЛ)		250Вт/E40/МГЛ		LBF
LBF 250 (ДНаТ)		250Вт/Е40/ДНаТ		LBF
LBF M LED		170Вт/LED		LBF
OPL/R 218 «С.Т.»		18Вт/G13/люм.		OPL 218,236,258
OPL/R 236 «С.Т.»		36Вт/G13/люм.		OPL 218,236,258
OPL/S 236 «С.Т.»		36Вт/G13/люм.		OPL 218,236,258
ЛПО-06В-2х18 «Сириус»		18Вт/G13/люм.		OPL 218,236,258
ЛПО-06В-2х36 «Сириус»		36Вт/G13/люм.		OPL 218,236,258
ЛПО 01-2х36-901 «Светотехника»		36Вт/G13/люм.		OPL 218,236,258
ЛПО 01-2х18-902 «Светотехника»		18Вт/G13/люм.		OPL 218,236,258
ЛВО 2х36 «Светотехника»		36Вт/G13/люм.		OPL 218,236,258
OPL/R 418 «С.Т.»		18Вт/G13/люм.		OPL 418, 436
OPL/R 836 «С.Т.»		36Вт/G13/люм.		OPL 418, 436
OPL/S 418 «С.Т.»		18Вт/G13/люм.		OPL 418, 436
OPL/S 436 «С.Т.»		36Вт/G13/люм.		OPL 418, 436
OPM/R 414 «С.Т.»		14Вт/G5/люм.		OPL 418, 436
ЛПО 01-4х18-903 «Светотехника»		18Вт/G13/люм.		OPL 418, 436
OWP/R 255 «С.Т.»		55Вт/2G11/люм.		OWP 255
OWP 336 «С.Т.»		36Вт/G13/люм.		OWP 336; OWP/R 418
OWP/R 236 (595) «С.Т.»		36Вт/2G11/люм.		OWP 336; OWP/R 418
OWP/R 236 «С.Т.»		36Вт/G13/люм.		OWP 336; OWP/R 418
OWP/R 418 «С.Т.»		18Вт/G13/люм.		OWP 336; OWP/R 418
ЛВО-01В-2х18 «Юпитер»		18Вт/G13/люм.		PRB 218, 336, 258
ЛПО-03В-2х18 «Юпитер»		18Вт/G13/люм.		PRB 218, 336, 258
ЛВО-01В-2х36 «Юпитер»		36Вт/G13/люм.		PRB 418, 436
ЛВО-01В-4х18 «Юпитер»		18Вт/G13/люм.		PRB 418, 436
ЛПО-03В-2х36 «Юпитер»		36Вт/G13/люм.		PRB 418, 436
ЛПО-03В-4х18 «Юпитер»		18Вт/G13/люм.		PRB 418, 436
ORO 418 RPP EI		18Вт/G13/люм.		PRB 418, 436
PRS/R 418 «С.Т.»		18Вт/G13/люм.		PRS 418, 436
WAVE LED 595		55Вт/LED		WAVE LED 595
LINER/R DR LED 1200		30Вт/LED		WAVE LED 595
ARCTIC SMC 136 «С.Т.»		36Вт/G13/люм.		ЛПП
ARCTIC SMC 236 «С.Т.»		36Вт/G13/люм.		ЛПП
ЛПП-04В-2х36 «Ватра»		36Вт/G13/люм.		ЛПП
ЛПП-07В-1х18 «Ватра»		18Вт/G13/люм.		ЛПП
ЛПП-07В-1х36 «Ватра»		36Вт/G13/люм.		ЛПП
ЛПП-07В-1х58 «Ватра»		58Вт/G13/люм.		ЛПП
ЛПП-07В-2х18 «Ватра»		18Вт/G13/люм.		ЛПП
ЛПП-07В-2х36 «Ватра»		36Вт/G13/люм.		ЛПП
ЛПП-07В-2х58 «Ватра»		58Вт/G13/люм.		ЛПП
ЛПП 01-902 2х18 «Светотехника»		18Вт/G13/люм.		ЛПП
ЛПП 01-901 2х36 «Светотехника»		36Вт/G13/люм.		ЛПП
ЛПП 01-905 1х18 «Светотехника»		18Вт/G13/люм.		ЛПП

Институт агроинженерии ФГБОУ ВО Южно-Уральский ГАУ

Кафедра «Менеджмент и информационные технологии»

МЕТОДИЧЕСКИЕ УКАЗАНИЯ и ЗАДАНИЯ

для контрольной работы по дисциплине

«Информационное обеспечение САПР»

Челябинск, 2017

Методические указания предназначены для обучающихся по программе бакалавриата по направлению подготовки 13.03.02

Составители:

Торбеев И.Г. – к.т.н., доцент (ЮУрГАУ)

Торбеева Е.А. – старший преподаватель (ЮУрГАУ)

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

Теоретическая часть.

Определение! ИМЯ – набор символов (слово) в Excel, обозначающие ячейку, диапазон ячеек, формулу или значение константы.

Возможность именования ячеек или диапазонов ячеек MS Excel – это отличный способ сделать читаемыми формулы на листе - создается не только естественное восприятие производимых вычислений, но и упрощается написание формул. Как видно из определения, имя можно задать для диапазона ячеек, функции, константы или таблицы. Обращение к именованному диапазону гораздо удобнее, чем прописывание адреса в формулах.

Ниже приведены обычные примеры имен:

=СУММ(C20:C30) или =СУММ(Продажи)

=ПРОИЗВЕД(A5,8.3) или =ПРОИЗВЕД(Цена,НСП)

У имен есть область действия, в которой они распознаются без дополнительных уточнений по своему расположению. Пусть имеется определенное имя, такое как Продажи, и его область определения — книга, то это имя распознается на всех листах этой книги, но не в какой-либо другой книге. Если на листе1 определено имя Бюджет, и его область действия ограничена этим листом, то для его применения на "Лист2" или "Лист3" необходимо будет указать Лист1!Бюджет.

Имя должно быть уникальным в своей области определения. Например, можно определить имя ВаловойДоход в областях "Лист1", "Лист2" и "Лист3" в одной и той же книге. Несмотря на одинаковость, каждое имя уникально в своей области определения.

При создании имени первым символом должна быть буква, знак подчеркивания или обратная косая черта (\). Остальные символы имени могут быть буквами, цифрами, точками и знаками подчеркивания.

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

Excel не различает строчные и прописные буквы в именах. Нельзя создать имя Продажи и затем создавать имя ПРОДАЖИ.

Для работы со всеми определенными в книге именами используется Диалоговое окно «Диспетчер имен». Например, если необходимо найти имена с ошибками, подтвердить значение и ссылку имени, посмотреть или изменить примечания или определить область действия. Можно отсортировать и отфильтровать список имен, а также легко добавить, изменить или удалить имена из одного расположения. Чтобы открыть диалоговое окно «Диспетчер имен», на вкладке «Формулы» в группе «Определенные имена» есть соответствующая кнопка.

Практическая часть.

Пример 1. Создание на листе имени для ячейки (или ячеек). Создайте расчетные данные по рисунку

1. Выделите диапазон B3:C5.

2. На вкладке «Формулы», в группе «Определенные имена» щелкните кнопку «Создать из выделенного фрагмента»

3. В окне «Создание имен из выделенного диапазона» установите флажок «в столбце слева» (см. рисунок)

Для контроля и редактирования имен ячеек (диапазонов) можно и расположения воспользоваться кнопкой «Диспетчер имен» на вкладке «Формулы», в группе «Определенные имена». Проверьте, что имеются три созданных имени ячеек, и закройте окно диспетчера.

4. Наберите ниже следующие данные: (столбцы A и B соответственно)

5. Произведем расчет делительного диаметра по формуле следующим образом:

· выделите ячейку С7, на вкладке «Формулы», в группе «Определенные имена» разверните кнопку «Использовать в формуле» и выберите имя m;

· нажмите клавишу «*», снова разверните кнопку «Использовать в формуле» и выберите имя z;

· нажмите клавишу «/», наберите выражение cos(радианы(

· разверните кнопку «Использовать в формуле» и выберите имя b

· наберите две скобки )) и закончите ввод формулы клавишей [Enter] .

В Excel аргументы тригонометрических функции должны быть в радианах, поэтому угол наклона b, который определен в градусах, заменяем радианами с помощью функции РАДИАНЫ.

6. Выделите ячейки B7:C7.

7. На вкладке «Формулы», в группе «Определенные имена» щелкните кнопку «Создать из выделенного фрагмента»

8. В окне «Создание имен из выделенного диапазона» установите флажок «в столбце слева» (см. рисунок)

9. Диаметры вершин и впадин рассчитайте аналогично, опираясь на рисунок:

Задание. Выполните расчеты на листе 2, используя имена ячеек.

Рассчитать коэффициент мощности по формуле
:

Исходные данные
Напряжение, В	Ul	10,5
Ток, А	Ikz	7,2
Активная мощность, Вт	P

Пример 2. Создание и использование имени диапазона. Вычислить значение максимального прогиба балки длиной h, от воздействия равномерно распределенной нагрузки q по формуле для следующих вариантов исходных данных: h=12 м, q=10000 Н/м, Е=2.06×10¹¹Па, I=1.3×10^-4 м². Для определения y использовать имена (или адреса) значений ячеек h, E, I. Результат оформить таблицей

q	h	E	I	y

Построить график изменения у от q (q изменяется от 10000 до 15000 с шагом 500)

Решение.

1. Введем исходные данные задачи, учитывая, что требуется построить график по независимой переменной q на лист 3:

2. Теперь выделим диапазон значений q, на вкладке Формулы щелкнем кнопку Создать из выделенного диапазона. В окне создания имени установить флажок «в строке сверху».

3. Выделим диапазон значений h, E, I (см. рисунок) и повторим действия создания имен из выделенного диапазона.

4. В ячейку с расчетом y введем формулу =5*q*h^4/(384*E*I)

5. Раскопируем формулу вниз соответственно переменной q

6. Выделим диапазоны значений q и y, удерживая клавишу [Ctrl], так как диапазоны несмежные.

7. На вкладке Вставка в группе ДиаграммывыберитеТочечный тип с гладкими кривыми и маркерами;удалите легенду; перенесите начало координат в точку (9000; 0).

Задание. Рассчитать число витков катушки на броневом цилиндрическом ферромагнитном сердечнике при различных значениях индуктивности L.

d₁=1см, d₂=2см, d₃=4см, d₄=5см, h₁=3см, h₂=4см, μ=10.
Для переменных d₁, d₂, d₃, d₄, h₁, h₂, μ использовать имена (или адреса) значений d1, d2, d3, d4, h1, h2, mu. Результат оформить таблицей

d1	d2	d3	d4	h1	h2	μ
L	A	B	F	w
3E+5

Построить график изменения w от L (L изменяется от 3·10⁵ нГн до 5·10⁵ нГн с шагом 5000 нГн).