Выполнение расчета с помощью функций ссылок и массивов
В диапазоне индексов помещения найдем номер строки того значения, которое больше расчетного (iрасч= 0,6407): в ячейку B16 вводим формулу =ПОИСКПОЗ(B10;Индексы_помещ;-1). Должно получиться число 9.
Замечание.
Для ввода в формулу имени диапазона
лучше использовать кнопку с выпадающим
списком
на вкладке «Формулы», а ячейки указывать
мышью.
Определим по найденному номеру его значение:
В ячейку B11 вводим формулу =ИНДЕКС(Индексы_помещ;B16). Должно получиться 0,8
Найдем номер строки в коэффициентах отражения, совпадающий с заданным. Для этого в ячейку B17 введем формулу =ПОИСКПОЗ(B3;Коэф_отр;0). Должно получиться 4
По марке светильника определим тип светильника:
в ячейку B18 введем формулу =ВПР(B2;Светильники;5;0)
Определим номер столбца в таблицах коэффициентов использования, совпадающий с типом светильника:
В ячейку B19 =ПОИСКПОЗ(B18;Таблицы!1:1;0)
Определим коэффициент использования для заданного типа светильника
В ячейку B12 введем формулу =ВПР(B11;Таблицы!$A$2:$AN$2;B17+B19)/100, где B17+B19 – номер столбца для заданного типа светильника и коэффициента отражения на листе ‘Таблицы’.
Определим световой поток светильника
В ячейку B13 введем формулу - =ВПР(B2;Светильники;6;0)
В ячейку B14 введем формулу - =B4*B5*B7*B8*B9/(B13*B12). Получим количество светильников. Измените фон этой ячейки.
Порядок заполнения
Выделить столбец B. В контекстном меню выбрать команду «Вставить» (не путайте с командой «Вставить» из буфера обмена). В результате будет добавлен новый столбец, с форматом первого столбца.
Теперь следует щелкнуть стрелку кнопки «Параметры добавления»
и выбрать вариант «Форматировать
как справа»
Заполнить согласно варианту новыми данными ячейки В2:В9. Формулы из ячеек С10:С14 и С16:С19 скопировать в столбец В.
Повторить п. 1-3 для заданного количества помещений.
Создайте новый рабочий лист «Сводная»
Создайте на этом рабочем листе «шапку» таблицы:
На листе «Расчет» выделите ячейки с названиями помещений и скопируйте их в буфер обмена.
Перейдите к листу «Сводная». Выделите ячейку А2, на вкладке «Главная» разверните кнопку «Вставить» и выберите команду «Транспонировать»:
На листе «Расчет» выделите ячейки с B2:F2 и скопируйте их.
Перейдите к листу «Сводная». Выделите ячейку В2, на вкладке «Главная» разверните кнопку «Вставить», выберите команду «Специальная вставка» и заполните окно по рисунку:
В столбце «Кол-во ламп» округлить полученное значение до ближайшего целого.
Выделите ячейку В1, на вкладке «Вставка» выберите команду «Сводная таблица». В диалоговое окно должно иметь вид:
Нажмите ОК. Заполните список полей сводной таблицы по рисунку:
Необходимые поля просто перетаскивайте мышью в нужную область: «Марка светильника» в «Названия строк», «Кол-во ламп» в «Значения». Сводная таблица появится на макете.
Исходные данные для расчетов.
№ варианта (последние цифры шифра) |
Таблица коэффициентов использования |
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 |
64 |
48 |
41 |
46 |
44 |
41 |
40 |
0,8 |
70 |
54 |
47 |
52 |
50 |
46 |
45 |
1 |
76 |
61 |
52 |
58 |
55 |
52 |
51 |
1,25 |
81 |
68 |
59 |
65 |
61 |
58 |
58 |
1,5 |
84 |
72 |
62 |
68 |
64 |
61 |
60 |
2 |
88 |
78 |
67 |
72 |
68 |
65 |
65 |
2,5 |
90 |
82 |
70 |
75 |
70 |
68 |
67 |
3 |
92 |
85 |
72 |
78 |
72 |
71 |
70 |
4 |
93 |
87 |
74 |
80 |
73 |
72 |
71 |
5 |
94 |
90 |
76 |
81 |
75 |
74 |
72 |
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 250S, 250H tulubar |
LB 150 HR (МГЛ) |
1 |
150Вт/RX7s/МГЛ |
12000 |
LB 250S, 250H tulubar |
LB 250 (МГЛ) |
1 |
250Вт/E40/МГЛ |
20000 |
LBF |
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 |
OPL 218,236,258 |
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 418, 436 |
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 |
OWP 255 |
OWP/R 255 «С.Т.» |
2 |
55Вт/2G11/люм. |
4800 |
OWP 336; OWP/R 418 |
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 |
PRB 218, 336, 258 |
ЛВО-01В-2х18 «Юпитер» |
2 |
18Вт/G13/люм. |
1200 |
PRB 218, 336, 258 |
ЛПО-03В-2х18 «Юпитер» |
2 |
18Вт/G13/люм. |
1200 |
PRB 418, 436 |
ЛВО-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 |
PRS 418, 436 |
PRS/R 418 «С.Т.» |
4 |
18Вт/G13/люм. |
1200 |
WAVE LED 595 |
WAVE LED 595 |
1 |
55Вт/LED |
3750 |
WAVE LED 595 |
LINER/R DR LED 1200 |
1 |
30Вт/LED |
2000 |
ЛПП |
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 |
Выбрать 10 помещений по предпоследней цифре шифра:
предпоследняя цифра шифра |
№ помещения |
1 |
1, 2, 3, 4, 5, 6, 7, 8, 9, 10 |
2 |
2, 3, 4, 5, 6, 7, 8, 9, 10, 11 |
3 |
3, 4, 5, 6, 7, 8, 9, 10, 11, 12 |
4 |
4, 5, 6, 7, 8, 9, 10, 11, 12, 13 |
5 |
5, 6, 7, 8, 9, 10, 11, 12, 13, 14 |
6 |
6, 7, 8, 9, 10, 11, 12, 13, 14,15 |
7 |
7, 8, 9, 10, 11, 12, 13, 14,15, 16 |
8 |
8, 9, 10, 11, 12, 13, 14,15, 16, 17 |
9 |
9, 10, 11, 12, 13, 14,15, 16, 17,18 |
0 |
10, 11, 12, 13, 14,15, 16, 17,18,19 |
№ помещения |
Помещение |
Kз |
Z |
длина, м |
ширина, м |
Высота. м |
|
|
Канцелярия |
1,4 |
1,1 |
6,6 |
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,2 |
1,15 |
2,5 |
0,9 |
3,0 |
|
|
Теплогенераторная |
1,4 |
1,1 |
6,0 |
2,2 |
2,2 |
|
|
Холл |
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 |
|
|
Лаборатория |
1,4 |
1,1 |
5,7 |
3,5 |
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,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,2 |
1,15 |
27,0 |
12,6 |
6,0 |
Коэффициенты отражения и наименьшую освещенность выбрать самостоятельно, по желанию.