Файл: контрольная работа №1 и №2.pdf

16 

•  Назначение и использование имен областей и диапазонов. 

В качестве контрольного примера приведено построение электронной формы 

ведомости заработной платы (см. приложение 2). 

В формируемой таблице ячейки только одной графы (так называемой графы 

ввода)  содержат  информацию,  вводимую  вручную.  Ячейки  остальных  граф 

должны  быть  заполнены  формулами,  использующими  вышеперечисленные 

функции. 

Вводимой  информацией  может  быть  перечень  фамилий  работников, 

количество отработанных часов, объем сделок и т.п. 

При отсутствии вводимой информации таблица выглядит пустой. Заполнение 

таблицы и расчет необходимых значений начинается сразу же при вводе первого 

элемента графы ввода. 

Для настройки таблицы решений используются следующие принципы. 

Формирование графы «Номер по порядку» 

В графе «№ п/п» сначала формируются 3 ячейки: 

В  ячейку  А10:  вводится  функция  ЕСЛИ,  проверяющая  наличие  вводимого 

значения (например, фамилии) в ячейке В10, т.е. если значение ячейки по данной 

строке  из  графы  "фамилия"  равно  "пусто" (=""), то  значение  текущей  ячейки 

также будет "пусто" (""), иначе А10=1.  

В  ячейку  А11:  вводится  функция  ЕСЛИ,  проверяющая  наличие  вводимого 

значения (например, фамилии) в ячейке В11, т.е. если ячейка по данной строке из 

графы "фамилия" равна  "пусто" (""), то в текущую ячейку ставится "пусто" (""), 

иначе ставится  <значение предыдущей ячейки сверху>  + 1 (А10+1). 

17 

Начиная  с  третьей  ячейки  графы  «№  п/п»  (А12),  следует  проверить 

выполнение нескольких условий (см.

Рис. 10 Схема формирования графы «Номер п/п» и 

Итого

):  

Будет ли в этой ячейке текст «Итого». Для этого должны одновременно  

выполняться 2 условия, соединенные логическим союзом «И»:  

а) - по графе "фамилия" ячейка предыдущей строки равна "пусто" ("");  

б) ячейка выше НЕ равна "пусто" ("")

1

. 

Если  хотя  бы  одно  условие  не  выполняется,  следует  проверить  наличие 

информации в соседней ячейке графы "фамилия" и если там значение "пусто", 

то в текущей ячейке ставится  (""). 

В  противном  случае  рассчитывается  очередное  значение  (порядковый  номер) 

по данной графе. 

да 

                                                 нет 

 да 

                                                 нет 

1

 Не равно обозначается символами  < > 

В11=”” 

И

 В10<> “” ? 

В12=””

?

А12=А11+1 

А12=”” 

А12=”Итого” 

18 

Рис. 10 Схема формирования графы «Номер п/п» и Итого 

Для проверки вариантов используется функция ЕСЛИ с вложенной функцией 

ЕСЛИ (см.

Рис. 11 Формирование графы «№ п/п»

). 

В дальнейшем при вводе каждого значения в графу «№ п/п» в ячейках А13- … 

слово «Итого» будет опускаться вниз соответственно добавлению информации. 

А 

В 

8 

№ п/п 

Фамилия 

9 

1 2 

10 

=ЕСЛИ(В10="";"";1) 

Иванов 

11 

=ЕСЛИ(В11="";"";А10+1)  

12 

=ЕСЛИ(И(В11="";В10<>"");"Итого";ЕСЛИ(В12="";"";А11+1))  

Рис. 11 Формирование графы «№ п/п» 

Последняя формула копируется вниз до конца таблицы. 

Затем вводятся формулы по остальным графам таблицы.  

Формирование графы 3 

В  последующих  графах  перед  требуемым  расчетом  сначала    проверяется 

наличие вводимого значения (например, фамилии) в графе ввода.  Если ячейка из 

графы ввода по данной строке равна "пусто" (""), то в текущую ячейку заносится 

"пусто" (""), иначе осуществляется расчет по заданным формулам. 

19 

В предлагаемом примере графа 3 тоже является графой ввода. Для студентов 

при  вводе  «Суммы  сделок»  возможно  применение  функции  СЛУЧМЕЖДУ,  где 

можно  задать  минимальное  и  максимальное  значения.  Тем  не  менее, 

предварительно  следует  проверить,  введена  ли  фамилия  в  ячейку  ввода: 

=ЕСЛИ(В10="";"";СЛУЧМЕЖДУ(7000;20000). 

Формирование графы 4. Функции просмотра 

Для подбора нужного значения (% сделок из графы 4) используются функции 

подбора значений ПРОСМОТР или ВПР, ГПР. Отличие между этими функциями 

состоит  в  возможности  точного  (ВПР,  ГПР  с  параметром  ЛОЖЬ)  или 

приближенного  (интервального - ПРОСМОТР)  поиска  совпадения.  Функции 

просмотра вызываются из категории Ссылки и массивы вкладки Формулы 

Функция  ПРОСМОТР  осуществляет  поиск  искомого  значения  в 

векторе_просмотра,  а  подбор  в  векторе_результата  (частный  случай - когда 

вектора располагаются рядом; тогда они рассматриваются как массив). Чтобы не 

ошибиться  в  правильном  указании  начального  и  конечного  адресов  диапазонов, 

следует воспользоваться присвоением имен областям. Для этого нужно выделить 

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

ПРИСВОИТЬ  ИМЯ 

из  вкладки 

ФОРМУЛЫ

,

либо

правой  кнопкой  мыши.  Чтобы  использовать  имя  области  в 

формуле, нужно нажать клавишу F3.  

В  данном  примере  можно  присвоить  имя  Таблице  2  (например,  Премия). 

Тогда  в  ячейку D10 надо  ввести  формулу  и  скопировать  ее  вниз  до  конца 

таблицы: 

А 

В 

С 

D 

Е 

8 

№ п/п 

Фамилия 

Сумма 
сделок 

% страхового вознаграждения 

Всего 

начислено 

9 

1 2 3 

4 

5 

10 

1 

Иванов 7500 

=ЕСЛИ(В10=”” ; ””; ПРОСМОТР(С10; Премия) 

20 

11 

=ЕСЛИ(В10=”” ; ””; ПРОСМОТР(С10; Премия)

12 

Итого  

=ЕСЛИ(В10=”” ; ””; ПРОСМОТР(С10; Премия)

Рис. 12  Использование функции ПРОСМОТР