ФГБОУ ВО «Южно-Уральский г осударственный а грарный у ниверситет»

ФГБОУ ВО «Южно-Уральский г осударственный а грарный у ниверситет»

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

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

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

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	5
80/50/30	4
80/30/10	3
70/50/20	2, 5
50/50/10	2
50/30/10	1, 5
30/30/10	1, 25
	1
	0, 8
	0, 6

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

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

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		1, 4	1, 1	5, 7	2, 9	2, 2
2	Канцелярия		1, 4	1, 1	6, 6	2, 8	2, 2
3	Лаборатория №1		1, 4	1, 1	2, 9	2, 8	3, 0
4	Лаборатория №2		1, 4	1, 1	3, 6	2, 8	2, 2
5	Рабочий зал		1, 4	1, 1	6, 4	4, 4	2, 2
6	Кабинет №2		1, 4	1, 1	3, 4	2, 8	2, 2
7	Лаборантская		1, 4	1, 1	5, 6	2, 7	3, 0
8	Комната отдыха		1, 4	1, 1	5, 7	3, 3	2, 2
9	Приемная		1, 4	1, 1	3, 7	3, 3	2, 2
10	Кабинет директора		1, 4	1, 1	6, 0	3, 7	2, 2
11	Кладовая		1, 4	1, 1	3, 7	3, 3	2, 2
12	Лестничная клетка		1, 4	1, 1	5, 6	2, 5	3, 0
13	Душевая №1		1, 2	1, 15	2, 5	0, 9	3, 0
14	Душевая №2		1, 4	1, 1	2, 6	1, 5	3, 0
15	Теплогенераторная		1, 4	1, 1	6, 0	2, 2	2, 2
16	Тамбур		1, 2	1, 15	2, 0	2, 0	3, 0
17	Холл		1, 4	1, 1	5, 7	3, 5	3, 0
18	Столовая		1, 4	1, 1	8, 2	2, 7	3, 0
19	Комната охраны		1, 4	1, 1	2, 8	2, 2	2, 2
20	Архив		1, 4	1, 1	4, 1	2, 2	2, 2
21	Бухгалтерия		1, 4	1, 1	6, 0	3, 3	2, 2
22	Лаборатория №3		1, 4	1, 1	5, 7	3, 5	2, 2
23	Аудитория №1		1, 4	1, 1	5, 7	2, 3	2, 2
24	Аудитория №2		1, 4	1, 1	4, 3	4, 1	2, 2
25	Аудитория №3		1, 4	1, 1	5, 9	3, 3	2, 2
26	Коридор		1, 4	1, 1	14, 8	1, 8	3, 0
27	Комната для инвентаря		1, 4	1, 1	3, 7	2, 0	3, 0
28	Кабинет главного инженера		1, 4	1, 1	3, 8	3, 7	2, 2
29	Служебный кабинет		1, 4	1, 1	3, 7	3, 0	2, 2
30	Комната мастеров		1, 4	1, 1	3, 7	3, 3	2, 2
31	Серверная		1, 4	1, 1	3, 7	2, 2	2, 2
32	Электротехническая мастерская		1, 4	1, 1	27, 0	5, 7	2, 2
33	Тамбур		1, 4	1, 1	5, 6	2, 5	3, 0
34	Терраса		1, 2	1, 15	27, 0	12, 6	6, 0

№ варианта (последние цифры шифра)	Таблица коэффициентов использования
1	LB 250S, 250H tulubar
2	LBF
3	OPL 218, 236, 258
4	OPL 418, 436
5	OWP 255
6	OWP 336; OWP/R 418
7	PRB 218, 336, 258
8	PRB 418, 436
9	PRS 418, 436
0	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	41	27	22	26	25	21	21
0, 8	47	33	27	32	30	26	26
1	51	38	31	36	34	30	30
1, 25	55	43	36	41	33	35	35
1, 5	58	47	39	44	41	38	38
2	61	52	43	48	45	42	41
2, 5	63	55	46	51	47	45	44
3	65	58	49	53	49	47	47
4	66	61	51	55	51	49	48
5	67	63	52	57	52	51	50

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	38	25	19	24	23	19	19
0, 8	44	31	25	29	28	24	24
1	48	35	28	33	31	28	27
1, 25	52	40	33	38	35	32	32
1, 5	54	44	36	41	38	35	35
2	57	48	40	45	41	39	38
2, 5	60	52	43	48	44	42	41
3	61	54	45	50	46	44	43
4	63	57	48	52	48	46	45
5	64	59	49	54	49	48	47

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	49	31	24	30	28	24	24
0, 8	55	38	31	36	38	30	30
1	60	44	36	42	39	35	34
1, 25	65	50	41	47	44	40	40
1, 5	68	55	45	51	48	44	43
2	72	61	50	56	52	49	48
2, 5	75	65	54	60	55	52	52
3	77	69	57	63	58	55	54
4	79	72	60	66	60	58	57
5	81	75	62	68	62	60	59

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	46	31	25	30	29	25	24
0, 8	53	39	32	37	35	31	31
1	58	44	37	42	40	36	36
1, 25	62	50	43	48	45	42	42
1, 5	65	55	46	51	48	45	45
2	68	59	50	55	51	49	48
2, 5	71	63	53	58	54	52	51
3	72	66	56	61	56	55	54
4	74	69	58	62	57	56	55
5	75	71	59	64	59	58	57

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	34	22	17	21	20	17	17
0, 8	39	27	22	26	24	21	21
1	42	31	25	29	28	25	24
1, 25	46	36	29	33	31	29	28
1, 5	48	39	32	36	34	31	31
2	51	43	36	40	37	35	34
2, 5	53	46	38	42	39	37	37
3	54	48	40	44	41	39	39
4	56	51	42	46	42	41	40
5	57	53	44	48	44	43	42

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	51	36	30	35	33	30	30
0, 8	58	44	37	42	40	36	36
1	62	49	42	47	44	41	41
1, 25	67	55	47	52	49	46	46
1, 5	70	59	51	56	52	50	49
2	73	64	55	59	55	53	53
2, 5	75	68	57	62	58	56	55
3	77	70	60	65	60	58	58
4	78	73	61	66	61	60	59
5	79	75	63	68	62	61	60

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	47	35	30	33	32	29	29
0, 8	52	41	35	39	38	35	35
1	56	46	39	43	41	39	38
1, 25	60	51	44	48	45	43	43
1, 5	62	54	46	50	47	46	45
2	64	57	49	53	50	48	48
2, 5	66	60	52	56	52	50	50
3	67	63	53	57	53	52	51
4	68	64	55	59	54	53	52
5	69	66	56	60	55	54	53

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	50	32	25	31	30	25	25
0, 8	57	40	32	33	36	32	31
1	62	46	38	44	41	37	36
1, 25	67	53	44	50	47	43	42
1, 5	70	57	48	54	50	47	46
2	74	63	53	58	54	51	50
2, 5	77	68	56	62	58	55	54
3	79	71	59	65	60	58	57
4	81	74	62	68	62	60	59
5	83	77	64	70	64	62	61

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	75	49	39	47	45	38	38
0, 8	86	61	49	58	55	48	48
1	94	70	57	66	62	56	55
1, 25	101	80	66	75	70	65	64
1, 5	106	86	72	81	76	70	69
2	112	95	80	88	82	78	76
2, 5	116	102	85	94	87	83	82
3	119	107	90	98	91	88	86
4	122	112	94	102	94	91	90
5	124	116	97	105	96	94	92

ЛПП	80/80/30	80/50/30	80/30/10	70/50/20	50/50/10	50/30/10	30/30/10
0, 6	41	23	17	23	22	17	17
0, 8	49	30	23	29	28	23	22
1	54	35	27	33	32	27	26
1, 25	59	41	32	39	36	31	31
1, 5	63	45	36	43	40	35	34
2	68	51	41	48	44	40	39
2, 5	71	56	46	52	48	44	42
3	74	61	49	56	51	47	45
4	77	65	53	59	54	50	48
5	79	69	56	62	56	53	51

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

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

ФГБОУ ВО «Южно-Уральский г осударственный а грарный у ниверситет»

12 3 4 5 6 Следующая ⇒