Файл: Методические указания к лабораторным работам Составитель Сухарев В. В. Москва ргу им. А. Н. Косыгина 2018 удк 003. 023.docx

Рисунок 6

Видно, что три позиции повторяются в таблице дважды, это «Кофта», «Жилет» и «Брюки».

Цены продажи для этих позиций также одинаковы.

Для того, чтобы исключить ручной труд при заполнении подобных таблиц, можно использовать функцию ВПР( ).

Функция ВПР( ) имеет следующие параметры

- искомое значение

- массив ячеек, в котором осуществляется поиск искомого значения и подставляемых данных

- номер столбца, из которого необходимо вернуть значение

- признак точного соответствия

При использовании функции ВПР( ) нужно помнить следующие моменты:

1. 
   Таблица, в которой осуществляется поиск, должна быть упорядочена по возрастанию значений первого столбца выделенной области;
   
2. 
   Поиск искомого значения осуществляется только по первому столбцу;
   
3. 
   Если таблица не упорядочена и знак точного соответствия не установлен, будет возвращено значение из строки, значение первого столбца которой близко (по мнению системы) на искомое значение;
   
4. 
   Нумерация столбцов, значения из которых подставляются как результат работы функции ВПР( ), ведется для выделенной области, а не по всему листу, на котором расположена справочная таблица.
   

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

	A	B	C	D	E	F
17				1234	Кофта	100
18				2222	Жилет	200
19				3333	Брюки	300
20				4444	Свитер	700

---

Рисунок 7

На рисунке 7 специально показано, что справочная таблица находится не по краям листа.

Теперь запишем функцию ВПР( ) для определения наименования номенклатуры для ячейки C3: = ВПР(В3;D17:F20;2;Истина)

Поиск позиции осуществляется по артикулу, поэтому в качестве искомого значения указывается значение ячейки С2. В первом столбце справочной таблицы также приведены артикулы номенклатуры.

Внимание! Если столбец, по которому осуществляется поиск, будет не первым, функция вернет не верный результат!

Второй параметр – это диапазон, в который входит вся справочная таблица. Строго говоря, для определения наименования нам достаточно указать только диапазон D17:E20.

Третий параметр – номер столбца в справочной таблице, из которого будет возвращено значение. Наша выделенная область начинается со столбца D, т.е. – это первый столбец справочной таблицы. Таким образом, столбец E является вторым столбцов выделенной области. Именно его значение и будет возвращено как результат работы функции.

Четвертый параметр желательно всегда указывать как Истина, чтобы минимизировать вероятность некорректной работы функции.

Если скопировать функцию вниз для всех заполненных ячеек таблицы, таблица примет вид, показанный на рисунке 8:

	A	B	C		D		E		F
1	Отчет о продажах
2	Дата	Артикул	Наименование		Цена		Количество		Сумма
3	01.10.2017	1234	Кофта		100		3		300
4	01.10.2017	2222	Жилет		200		2		400
5	01.10.2017	3333	Брюки		300		5		1500
6	02.10.2017	1234	#Н/Д		100		2		200
7	02.10.2017	2222	#Н/Д		200		3		600
8	02.10.2017	3333	#Н/Д		300		6		1800
9	02.10.2017	4444	#Н/Д		700		1		700

---

Рисунок 8

Из таблицы, что для части строк заполнение выполнилось, а для части – выведено сообщение об ошибке «#Н/Д» (нет данных).

Чтобы понять причину возникновения данной ошибки, установиv курсор в ячейку C6. И обратим внимание на второй параметр функции. Произошло смещение диапазона поиска. В самом деле, в функции указана относительная адресация, поэтому при копировании функции ВПР ( ) вниз по таблице произошло аналогичное смещение и строк диапазона поиска.

Возникает вопрос, а почему в этом случае были найдены значения для двух следующих строк таблицы? Все просто, смещение диапазона произошло и для этих двух строк, но искомое значение попало в получившийся диапазон.

Чтобы устранить последствия этой ошибки, укажем в формуле для первой ячейки во втором параметре признак абсолютной адресации как для строк, так и для столбцов. В данном случае указание абсолютной адресации для столбцов является избыточным (не ошибочным!), но будет использовано чуть позднее.

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

	A	B	C	D	E	F
1	Отчет о продажах
2	Дата	Артикул	Наименование	Цена	Количество	Сумма
3	01.10.2017	1234	Кофта	100	3	300
4	01.10.2017	2222	Жилет	200	2	400
5	01.10.2017	3333	Брюки	300	5	1500
6	02.10.2017	1234	Кофта	100	2	200
7	02.10.2017	2222	Жилет	200	3	600
8	02.10.2017	3333	Брюки	300	6	1800
9	02.10.2017	4444	Свитер	700	1	700

---

Рисунок 9

В таблице, показанной на рисунке 9, все наименования заполнены автоматически и при изменении артикула будут заменены на те значения, что будут соответствовать вновь введенному артикулу. Но цены позиций номенклатуры указаны вручную. Чтобы этого избежать и обеспечить автоматическое заполнение цен, скопируем функцию из ячейки C3 в ячейку D3, предварительно добавив признак абсолютной адресации перед столбцом первого параметра функции ВПР( ), а также изменив номер столбца, из которого возвращается значение, со второго на третий.

И растянем формулу вниз. Если все сделано правильно, внешний вид таблиц не изменится.

Формула для ячейки D3 имеет вид: = ВПР($В3;D17:F20;3;Истина)

Для проверки правильности работы формул добавим в нашу таблицу несколько строк, «протянем» автозаполнение столбцов С и В на добавленные строки и в столбце В введем значения артикулов, присутствующих в справочной таблице. Таблица примет вид, похожий на тот, что приведен на рисунке 10:

	A	B	C	D	E	F
1	Отчет о продажах
2	Дата	Артикул	Наименование	Цена	Количество	Сумма
3	01.10.2017	1234	Кофта	100	3	300
4	01.10.2017	2222	Жилет	200	2	400
5	01.10.2017	3333	Брюки	300	5	1500
6	02.10.2017	1234	Кофта	100	2	200
7	02.10.2017	2222	Жилет	200	3	600
8	02.10.2017	3333	Брюки	300	6	1800
9	02.10.2017	4444	Свитер	700	1	700
10		4444	Свитер	700
11			#Н/Д	#Н/Д
12		3333	Брюки	300
13
14

---

Рисунок 10

---