Выполнение расчета с помощью функций ссылок и массивов

1.В диапазоне индексов помещения найдем номер строки того значения, которое больше расчетного (i_расч= 0,6407): в ячейку B16 вводим формулу =ПОИСКПОЗ(B10;Индексы_помещ;-1).Должно получиться число 9.

Замечание. Для ввода в формулу имени диапазона лучше использовать кнопку с выпадающим списком на вкладке «Формулы», а ячейки указывать мышью.

2. Определим по найденному номеру его значение:

В ячейку B11 вводим формулу =ИНДЕКС(Индексы_помещ;B16).Должно получиться 0,8

3.Найдем номер строки в коэффициентах отражения, совпадающий с заданным. Для этого в ячейку B17введем формулу=ПОИСКПОЗ(B3;Коэффициенты_отражения;0).Должно получиться 4

4. По марке светильника определим тип светильника:

в ячейку B18 введем формулу =ВПР(B2;Светильники;5;0)

5. Определим номер столбца в таблицах коэффициентов использования, совпадающий с типом светильника:

В ячейку B19=ПОИСКПОЗ(B18;Таблицы!1:1;0)

6. Определим коэффициент использования для заданного типа светильника

В ячейку B12 введем формулу =ВПР(B11;Таблицы!$A$2:$AN$2;B17+B19)/100,где B17+B19 – номер столбца для заданного типа светильника и коэффициента отражения на листе ‘Таблицы’.

7. Определим световой поток светильника

В ячейку B13 введем формулу - =ВПР(B2;Светильники;6;0)

В ячейку B14 введем формулу - =B4*B5*B7*B8*B9/(B13*B12). Получим количество ламп. Измените фон этой ячейки.

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

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	OWP/R 418 «С.Т.»	1,4	1,1	5,7	2,9	2,2
	Канцелярия	OWP/R 418 «С.Т.»	1,4	1,1	6,6	2,8	2,2
	Лаборатория №1	OWP/R 418 «С.Т.»	1,4	1,1	2,9	2,8	3,0
	Лаборатория №2	OWP/R 418 «С.Т.»	1,4	1,1	3,6	2,8	2,2
	Рабочий зал	OWP/R 418 «С.Т.»	1,4	1,1	6,4	4,4	2,2
	Кабинет №2	PRS/R 418 «С.Т.»	1,4	1,1	3,4	2,8	2,2
	Лаборантская	PRS/R 418 «С.Т.»	1,4	1,1	5,6	2,7	3,0
	Комната отдыха	PRS/R 418 «С.Т.»	1,4	1,1	5,7	3,3	2,2
	Приемная	PRS/R 418 «С.Т.»	1,4	1,1	3,7	3,3	2,2
	Кабинет директора	PRS/R 418 «С.Т.»	1,4	1,1	6,0	3,7	2,2
	Кладовая	PRS/R 418 «С.Т.»	1,4	1,1	3,7	3,3	2,2
	Лестничная клетка	ЛПП-07В-2х36 «Ватра»	1,4	1,1	5,6	2,5	3,0
	Душевая №1	НББ-64В-60 «Селена»	1,2	1,15	2,5	0,9	3,0
	Душевая №2	ЛПП-07В-1х36 «Ватра»	1,4	1,1	2,6	1,5	3,0
	Теплогенераторная	ЛПП-07В-2х58 «Ватра»	1,4	1,1	6,0	2,2	2,2
	Тамбур	НББ-64В-60 «Селена»	1,2	1,15	2,0	2,0	3,0
	Холл	PRS/R 418 «С.Т.»	1,4	1,1	5,7	3,5	3,0
	Столовая	PRS/R 418 «С.Т.»	1,4	1,1	8,2	2,7	3,0
	Комната охраны	PRS/R 418 «С.Т.»	1,4	1,1	2,8	2,2	2,2
	Архив	OWP/R 418 «С.Т.»	1,4	1,1	4,1	2,2	2,2
	Бухгалтерия	PRS/R 418 «С.Т.»	1,4	1,1	6,0	3,3	2,2
	Лаборатория №3	PRS/R 418 «С.Т.»	1,4	1,1	5,7	3,5	2,2
	Аудитория №1	PRS/R 418 «С.Т.»	1,4	1,1	5,7	2,3	2,2
	Аудитория №2	PRS/R 418 «С.Т.»	1,4	1,1	4,3	4,1	2,2
	Аудитория №3	PRS/R 418 «С.Т.»	1,4	1,1	5,9	3,3	2,2
	Коридор	PRS/R 418 «С.Т.»	1,4	1,1	14,8	1,8	3,0
	Комната для инвентаря	ЛПП-07В-2х36 «Ватра»	1,4	1,1	3,7	2,0	3,0
	Кабинет главного инженера	PRS/R 418 «С.Т.»	1,4	1,1	3,8	3,7	2,2
	Служебный кабинет	PRS/R 418 «С.Т.»	1,4	1,1	3,7	3,0	2,2
	Комната мастеров	PRS/R 418 «С.Т.»	1,4	1,1	3,7	3,3	2,2
	Серверная	PRS/R 418 «С.Т.»	1,4	1,1	3,7	2,2	2,2
	Электротехническая мастерская	ЛПП-07В-2х58 «Ватра»	1,4	1,1	27,0	5,7	2,2
	Тамбур	ЛПП-07В-2х58 «Ватра»	1,4	1,1	5,6	2,5	3,0
	Терраса	НСП11-100 «Ватра»	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/люм.		ЛПП