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

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$12;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		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/люм.		ЛПП